[Commits] cab1d63: Merge branch '10.3' into 10.4

revision-id: cab1d6382623f0611335caf2cd056aa7ee04d7cd (mariadb-10.3.6-13-gcab1d63) parent(s): ffe83e8e7bef32eb2a80aad2d382f0b023dd3a44 ee5124d714ea01f4e1bd6decf6da38b05c1009ad author: Igor Babaev committer: Igor Babaev timestamp: 2018-06-03 10:34:41 -0700 message: Merge branch '10.3' into 10.4 .gitattributes | 1 + .gitignore | 2 + BUILD/SETUP.sh | 2 +- BUILD/compile-alpha | 24 - BUILD/compile-alpha-debug | 12 - BUILD/compile-amd64-debug-wsrep | 0 BUILD/compile-amd64-wsrep | 0 BUILD/compile-ia64-debug-max | 25 - BUILD/compile-innodb | 25 - BUILD/compile-pentium | 26 - BUILD/compile-pentium-cybozu | 24 - BUILD/compile-pentium-debug | 24 - BUILD/compile-pentium-debug-all | 10 - BUILD/compile-pentium-debug-max | 24 - BUILD/compile-pentium-debug-max-no-embedded | 25 - BUILD/compile-pentium-debug-openssl | 26 - BUILD/compile-pentium-debug-wsrep | 12 - BUILD/compile-pentium-debug-yassl | 26 - BUILD/compile-pentium-gcov | 45 - BUILD/compile-pentium-gprof | 24 - BUILD/compile-pentium-icc-valgrind-max | 63 - BUILD/compile-pentium-icc-yassl | 0 BUILD/compile-pentium-max | 25 - BUILD/compile-pentium-valgrind-max | 38 - BUILD/compile-pentium-wsrep | 11 - BUILD/compile-pentium32 | 26 + BUILD/compile-pentium32-cybozu | 24 + BUILD/compile-pentium32-debug | 24 + BUILD/compile-pentium32-debug-max | 24 + BUILD/compile-pentium32-debug-openssl | 26 + BUILD/compile-pentium32-gcov | 45 + BUILD/compile-pentium32-gprof | 24 + BUILD/compile-pentium32-icc-valgrind-max | 63 + BUILD/compile-pentium32-max | 25 + BUILD/compile-pentium32-valgrind-max | 38 + BUILD/compile-pentium32-wsrep | 11 + BUILD/compile-pentium64-wsrep | 0 BUILD/compile-solaris-x86-32 | 11 - BUILD/compile-solaris-x86-32-debug | 11 - BUILD/compile-solaris-x86-32-debug-forte | 27 - BUILD/compile-solaris-x86-forte-32 | 19 - CMakeLists.txt | 5 +- VERSION | 4 +- client/client_priv.h | 1 + client/mysql.cc | 5 +- client/mysql_plugin.c | 9 +- client/mysql_upgrade.c | 7 +- client/mysqlbinlog.cc | 2 +- client/mysqldump.c | 50 +- client/mysqltest.cc | 10 +- cmake/crc32-vpmsum.cmake | 5 - cmake/crc32.cmake | 5 + cmake/mariadb_connector_c.cmake | 4 + cmake/numa.cmake | 27 +- cmake/os/Windows.cmake | 2 - cmake/os/WindowsCache.cmake | 6 +- config.h.cmake | 13 +- configure.cmake | 22 +- debian/additions/debian-start | 2 + debian/additions/innotop/changelog.innotop | 2 +- debian/additions/innotop/innotop | 14 +- debian/additions/innotop/innotop.1 | 6 +- debian/additions/my.cnf | 1 + debian/apparmor-profile | 4 +- debian/autobake-deb.sh | 16 +- debian/control | 31 +- debian/mariadb-server-10.3.install | 2 + debian/mariadb-server-10.3.mysql.default | 15 + debian/mariadb-server-10.3.postinst | 6 +- debian/mysql-common.install | 1 - debian/patches/00list | 5 - ...pts__mysql_create_system_tables__no_test.dpatch | 38 - .../38_scripts__mysqld_safe.sh__signals.dpatch | 43 - ...41_scripts__mysql_install_db.sh__no_test.dpatch | 20 - debian/patches/50_mysql-test__db_test.dpatch | 24 - .../61_replace_dash_with_bash_mbug675185.dpatch | 20 - debian/rules | 68 +- extra/crc32-vpmsum/CMakeLists.txt | 11 +- extra/crc32-vpmsum/clang_workaround.h | 82 + extra/crc32-vpmsum/crc32.iS | 734 --- extra/crc32-vpmsum/crc32_wrapper.ic | 52 - extra/crc32-vpmsum/crc32c.S | 14 - extra/crc32-vpmsum/crc32c_constants.h | 2037 +++++--- extra/crc32-vpmsum/crc32c_wrapper.c | 78 - extra/crc32-vpmsum/crc32ieee.S | 14 - extra/crc32-vpmsum/crc32ieee_constants.h | 2041 +++++--- extra/crc32-vpmsum/crc32ieee_wrapper.c | 75 - extra/crc32-vpmsum/ppc-opcode.h | 23 - extra/crc32-vpmsum/vec_crc32.c | 674 +++ extra/innochecksum.cc | 16 +- extra/mariabackup/CMakeLists.txt | 2 +- extra/mariabackup/backup_copy.cc | 8 +- extra/mariabackup/backup_mysql.cc | 60 +- extra/mariabackup/changed_page_bitmap.cc | 1 + extra/mariabackup/crc/crc-intel-pclmul.c | 2 +- extra/mariabackup/ds_tmpfile.c | 18 +- extra/mariabackup/fil_cur.cc | 7 +- extra/mariabackup/fil_cur.h | 1 + extra/mariabackup/xb0xb.h | 27 - extra/mariabackup/xtrabackup.cc | 236 +- extra/replace.c | 2 +- include/hash.h | 2 +- include/heap.h | 2 +- include/m_ctype.h | 2 +- include/maria.h | 2 +- include/my_base.h | 2 +- include/my_dir.h | 2 +- include/my_global.h | 127 +- include/my_sys.h | 21 +- include/my_time.h | 3 +- include/myisam.h | 13 +- include/mysql/psi/mysql_file.h | 707 +-- include/mysql/psi/mysql_idle.h | 2 +- include/mysql/psi/mysql_socket.h | 30 +- include/mysql/psi/mysql_statement.h | 18 +- include/mysql/psi/mysql_table.h | 8 +- include/mysql/psi/mysql_thread.h | 38 +- include/mysql/psi/psi.h | 16 + include/mysql/psi/psi_abi_v1.h.pp | 1 + include/mysql/psi/psi_abi_v2.h.pp | 1 + libmariadb | 2 +- libmysqld/CMakeLists.txt | 2 + man/make_win_bin_dist.1 | 176 - mysql-test/include/check-testcase.test | 5 +- mysql-test/include/galera_wait_ready.inc | 34 +- mysql-test/include/have_rbr_triggers.inc | 5 - mysql-test/lib/My/CoreDump.pm | 39 + mysql-test/lib/My/SafeProcess/safe_process.cc | 64 +- mysql-test/lib/mtr_report.pm | 2 +- mysql-test/main/alter_table.result | 64 +- mysql-test/main/alter_table.test | 35 +- mysql-test/main/alter_table_errors.result | 10 + mysql-test/main/alter_table_errors.test | 10 + mysql-test/main/alter_table_online.result | 2 +- mysql-test/main/ansi.result | 70 + mysql-test/main/ansi.test | 33 + mysql-test/main/assign_key_cache-5405.result | 19 - mysql-test/main/assign_key_cache-5405.test | 27 - mysql-test/main/assign_key_cache.result | 13 + mysql-test/main/assign_key_cache.test | 13 + mysql-test/main/assign_key_cache_debug.result | 19 + mysql-test/main/assign_key_cache_debug.test | 27 + mysql-test/main/check.result | 33 + mysql-test/main/check.test | 24 + mysql-test/main/check_constraint.result | 41 + mysql-test/main/check_constraint.test | 24 + mysql-test/main/column_compression.result | 105 +- mysql-test/main/column_compression.test | 81 +- mysql-test/main/column_compression_utf16.result | 9 + mysql-test/main/column_compression_utf16.test | 9 + mysql-test/main/connect_debug.result | 5 + mysql-test/main/connect_debug.test | 12 + mysql-test/main/create_or_replace.result | 20 + mysql-test/main/create_or_replace.test | 25 + mysql-test/main/cte_recursive.result | 220 + mysql-test/main/cte_recursive.test | 204 + mysql-test/main/ctype_create.result | 4 + mysql-test/main/ctype_create.test | 4 + mysql-test/main/ctype_latin1_de-master.opt | 1 - mysql-test/main/ctype_latin1_de.result | 6 +- mysql-test/main/ctype_latin1_de.test | 5 + mysql-test/main/ctype_ucs.result | 32 + mysql-test/main/ctype_ucs.test | 23 + mysql-test/main/ctype_utf8mb4.result | 23 + mysql-test/main/ctype_utf8mb4.test | 19 + mysql-test/main/custom_aggregate_functions.result | 206 + mysql-test/main/custom_aggregate_functions.test | 182 + mysql-test/main/delayed.result | 1 - mysql-test/main/derived_cond_pushdown.result | 508 +- mysql-test/main/derived_cond_pushdown.test | 141 + mysql-test/main/derived_view.result | 4 +- mysql-test/main/distinct.result | 10 + mysql-test/main/distinct.test | 8 + mysql-test/main/drop_bad_db_type.result | 2 + mysql-test/main/explain_slowquerylog.result | 4 + mysql-test/main/explain_slowquerylog.test | 6 + mysql-test/main/features.result | 2 + mysql-test/main/func_math.result | 391 +- mysql-test/main/func_math.test | 240 +- mysql-test/main/func_time.result | 53 + mysql-test/main/func_time.test | 78 + mysql-test/main/grant.result | 5 - mysql-test/main/grant.test | 8 - mysql-test/main/grant2.result | 3 +- mysql-test/main/grant2.test | 3 +- mysql-test/main/grant_not_windows.result | 8 + mysql-test/main/grant_not_windows.test | 14 + mysql-test/main/group_by.result | 11 + mysql-test/main/group_by.test | 9 + mysql-test/main/implicit_commit.result | 4 +- mysql-test/main/in_subq_cond_pushdown.result | 21 +- mysql-test/main/insert_select.result | 9 + mysql-test/main/insert_select.test | 10 + mysql-test/main/intersect.result | 60 + mysql-test/main/intersect.test | 38 + mysql-test/main/invisible_field.result | 66 + mysql-test/main/invisible_field.test | 33 + .../main/invisible_field_grant_completely.result | 68 + .../main/invisible_field_grant_completely.test | 57 + .../main/invisible_field_grant_system.result | 68 + mysql-test/main/invisible_field_grant_system.test | 52 + mysql-test/main/invisible_partition.result | 18 + mysql-test/main/invisible_partition.test | 24 + mysql-test/main/limit_rows_examined.result | 2 - mysql-test/main/multi_update.result | 72 + mysql-test/main/multi_update.test | 46 + mysql-test/main/myisam_recover.result | 2 - mysql-test/main/mysql.test | 16 +- mysql-test/main/mysql_client_test.result | 122 + mysql-test/main/mysql_client_test.test | 7 + mysql-test/main/mysql_cp932.test | 42 +- mysql-test/main/mysqld--help.result | 24 +- mysql-test/main/mysqldump.result | 8 + mysql-test/main/mysqldump.test | 9 + mysql-test/main/olap.result | 14 + mysql-test/main/olap.test | 17 + mysql-test/main/parser.result | 331 ++ mysql-test/main/parser.test | 64 + mysql-test/main/partition_alter.result | 2 + mysql-test/main/partition_exchange.result | 11 + mysql-test/main/partition_exchange.test | 13 + mysql-test/main/partition_innodb.test | 4 - mysql-test/main/partition_list.result | 210 + mysql-test/main/partition_list.test | 41 + mysql-test/main/partition_open_files_limit.test | 4 + mysql-test/main/partition_sync.result | 25 + mysql-test/main/partition_sync.test | 36 + mysql-test/main/ps.result | 11 + mysql-test/main/ps.test | 15 + mysql-test/main/ps_qc_innodb.result | 29 + mysql-test/main/ps_qc_innodb.test | 35 + mysql-test/main/read_only_innodb.result | 8 + mysql-test/main/read_only_innodb.test | 9 + mysql-test/main/rename.result | 66 + mysql-test/main/rename.test | 46 + mysql-test/main/sp-anchor-row-type-table.result | 2 +- mysql-test/main/sp-anchor-type.result | 16 +- mysql-test/main/sp-bugs.result | 50 + mysql-test/main/sp-bugs.test | 50 + mysql-test/main/sp-code.result | 21 + mysql-test/main/sp-code.test | 19 + mysql-test/main/sp-destruct.result | 7 + mysql-test/main/sp-destruct.test | 11 + mysql-test/main/sp-expr.result | 153 + mysql-test/main/sp-expr.test | 159 + mysql-test/main/sp-innodb.result | 34 + mysql-test/main/sp-innodb.test | 42 + mysql-test/main/sp-row.result | 68 +- mysql-test/main/sp-vars.result | 69 + mysql-test/main/sp-vars.test | 67 + mysql-test/main/sp.result | 115 + mysql-test/main/sp.test | 76 + mysql-test/main/statement-expr.result | 67 + mysql-test/main/statement-expr.test | 80 + mysql-test/main/statistics_close.result | 11 + mysql-test/main/statistics_close.test | 18 + mysql-test/main/status.result | 12 + mysql-test/main/status.test | 7 + mysql-test/main/subselect-crash_15755.result | 317 ++ mysql-test/main/subselect-crash_15755.test | 60 + mysql-test/main/subselect4.result | 20 + mysql-test/main/subselect4.test | 23 + mysql-test/main/subselect_extra.result | 4 +- mysql-test/main/subselect_sj.result | 26 + mysql-test/main/subselect_sj.test | 30 + mysql-test/main/subselect_sj_jcl6.result | 26 + mysql-test/main/table_value_constr.result | 26 + mysql-test/main/table_value_constr.test | 31 + mysql-test/main/temporal_literal.result | 1 - mysql-test/main/trigger.result | 13 +- mysql-test/main/trigger.test | 14 +- mysql-test/main/type_int.result | 132 + mysql-test/main/type_int.test | 91 + mysql-test/main/union.result | 14 + mysql-test/main/union.test | 15 + mysql-test/main/win.result | 15 + mysql-test/main/win.test | 16 + mysql-test/main/win_percentile.result | 24 + mysql-test/main/win_percentile.test | 25 + mysql-test/mysql-test-run.pl | 103 +- mysql-test/r/ps_qc_innodb.result | 29 - mysql-test/suite.pm | 3 + mysql-test/suite/archive/discover.result | 8 + mysql-test/suite/binlog/r/binlog_stm_sp.result | 88 + mysql-test/suite/binlog/t/binlog_stm_sp.test | 41 + .../suite/binlog_encryption/encrypted_master.test | 1 + .../suite/binlog_encryption/encrypted_slave.test | 1 + mysql-test/suite/binlog_encryption/testdata.opt | 1 - .../suite/compat/oracle/r/binlog_stm_ps.result | 31 + .../suite/compat/oracle/r/binlog_stm_sp.result | 33 + .../compat/oracle/r/column_compression.result | 9 + .../suite/compat/oracle/r/func_concat.result | 67 + mysql-test/suite/compat/oracle/r/func_time.result | 31 + mysql-test/suite/compat/oracle/r/gis.result | 6 + mysql-test/suite/compat/oracle/r/parser.result | 432 ++ .../oracle/r/sp-anchor-row-type-table.result | 2 +- mysql-test/suite/compat/oracle/r/sp-expr.result | 158 + mysql-test/suite/compat/oracle/r/sp-param.result | 16 +- mysql-test/suite/compat/oracle/r/sp-row.result | 68 +- mysql-test/suite/compat/oracle/r/sp.result | 16 +- .../suite/compat/oracle/r/statement-expr.result | 69 + .../compat/oracle/r/table_value_constr.result | 2101 ++++++++ mysql-test/suite/compat/oracle/r/versioning.result | 16 + mysql-test/suite/compat/oracle/r/win.result | 17 + .../suite/compat/oracle/t/binlog_stm_ps.test | 20 + .../suite/compat/oracle/t/binlog_stm_sp.test | 23 + .../suite/compat/oracle/t/column_compression.test | 11 + mysql-test/suite/compat/oracle/t/func_concat.test | 32 + mysql-test/suite/compat/oracle/t/func_time.test | 25 + mysql-test/suite/compat/oracle/t/gis.test | 4 + mysql-test/suite/compat/oracle/t/parser.test | 186 + mysql-test/suite/compat/oracle/t/sp-expr.test | 165 + .../suite/compat/oracle/t/statement-expr.test | 86 + .../suite/compat/oracle/t/table_value_constr.test | 1083 ++++ mysql-test/suite/compat/oracle/t/versioning.test | 13 + mysql-test/suite/compat/oracle/t/win.test | 22 + .../encryption/r/innodb-bad-key-change3.result | 1 + .../encryption/r/innodb-checksum-algorithm.result | 3 + .../r/innodb-discard-import-change.result | 1 + .../encryption/r/innodb-discard-import.result | 2 + .../r/innodb_encryption_discard_import.result | 2 + .../suite/encryption/t/innodb-scrub-log.test | 2 +- .../t/innodb_encryption_discard_import.test | 1 + .../suite/federated/assisted_discovery.result | 8 - mysql-test/suite/federated/assisted_discovery.test | 9 - .../suite/federated/federatedx_versioning.result | 100 + .../suite/federated/federatedx_versioning.test | 77 + mysql-test/suite/federated/timestamps.result | 64 + mysql-test/suite/federated/timestamps.test | 45 + .../suite/funcs_1/r/is_columns_innodb.result | 12 +- .../suite/funcs_1/r/is_columns_memory.result | 12 +- .../suite/funcs_1/r/is_columns_myisam.result | 12 +- .../funcs_1/r/is_columns_myisam_embedded.result | 12 +- mysql-test/suite/galera/disabled.def | 8 +- .../suite/galera/include/galera_load_provider.inc | 4 +- .../suite/galera/include/galera_st_clean_slave.inc | 4 +- .../suite/galera/include/galera_st_kill_slave.inc | 5 +- .../galera/include/galera_st_kill_slave_ddl.inc | 4 +- .../galera/include/galera_st_shutdown_slave.inc | 4 +- mysql-test/suite/galera/include/start_mysqld.inc | 9 +- mysql-test/suite/galera/r/MW-44.result | 18 +- .../suite/galera/r/galera_encrypt_tmp_files.result | 37 + mysql-test/suite/galera/r/galera_gra_log.result | 2 +- .../suite/galera/r/galera_ist_mysqldump.result | 38 +- mysql-test/suite/galera/r/galera_ist_rsync.result | 2 + .../suite/galera/r/galera_kill_nochanges.result | 4 + mysql-test/suite/galera/r/galera_mdev_15611.result | 16 + .../suite/galera/r/galera_toi_ddl_fk_insert.result | 10 + .../galera/r/galera_var_auto_inc_control_on.result | 31 +- .../galera/r/galera_var_retry_autocommit.result | 84 +- mysql-test/suite/galera/r/mysql-wsrep#33.result | 15 + mysql-test/suite/galera/r/pxc-421.result | 1 - mysql-test/suite/galera/t/MW-44.test | 9 +- .../suite/galera/t/galera_encrypt_tmp_files.cnf | 8 + .../suite/galera/t/galera_encrypt_tmp_files.test | 57 + mysql-test/suite/galera/t/galera_gcs_fragment.test | 7 + mysql-test/suite/galera/t/galera_gra_log.test | 3 +- .../suite/galera/t/galera_ist_mysqldump.test | 5 + .../suite/galera/t/galera_kill_nochanges.test | 8 + mysql-test/suite/galera/t/galera_mdev_15611.cnf | 5 + mysql-test/suite/galera/t/galera_mdev_15611.test | 30 + mysql-test/suite/galera/t/galera_pc_ignore_sb.test | 6 +- .../galera/t/galera_var_auto_inc_control_on.test | 29 +- .../suite/galera/t/galera_var_dirty_reads.test | 1 + .../galera/t/galera_var_retry_autocommit.test | 133 +- mysql-test/suite/galera/t/pxc-421.test | 2 - .../suite/gcol/r/innodb_virtual_basic.result | 2 - .../suite/gcol/r/innodb_virtual_index.result | 17 +- mysql-test/suite/gcol/t/innodb_virtual_index.test | 8 + mysql-test/suite/handler/heap.result | 21 +- mysql-test/suite/handler/heap.test | 20 +- mysql-test/suite/handler/innodb.test | 5 - mysql-test/suite/handler/interface.result | 22 + mysql-test/suite/handler/interface.test | 25 + mysql-test/suite/innodb/include/alter_instant.inc | 33 + mysql-test/suite/innodb/include/alter_nocopy.inc | 33 + .../suite/innodb/include/alter_nocopy_fail.inc | 51 + .../suite/innodb/r/alter_algorithm,COPY.rdiff | 92 + .../suite/innodb/r/alter_algorithm,INPLACE.rdiff | 66 + .../suite/innodb/r/alter_algorithm,INSTANT.rdiff | 78 + mysql-test/suite/innodb/r/alter_algorithm.result | 71 + mysql-test/suite/innodb/r/alter_copy.result | 3 + mysql-test/suite/innodb/r/alter_crash.result | 3 + .../suite/innodb/r/alter_foreign_crash.result | 26 + mysql-test/suite/innodb/r/alter_instant,COPY.rdiff | 61 + .../suite/innodb/r/alter_instant,INPLACE.rdiff | 11 + .../suite/innodb/r/alter_instant,INSTANT.rdiff | 11 + mysql-test/suite/innodb/r/alter_instant.result | 50 + mysql-test/suite/innodb/r/alter_kill.result | 78 + .../suite/innodb/r/alter_missing_tablespace.result | 23 +- mysql-test/suite/innodb/r/alter_not_null.result | 88 + .../suite/innodb/r/alter_not_null_debug.result | 68 + mysql-test/suite/innodb/r/alter_partitioned.result | 10 + .../suite/innodb/r/alter_partitioned_debug.result | 27 + .../suite/innodb/r/alter_partitioned_xa.result | 18 + .../suite/innodb/r/alter_rename_files.result | 20 + mysql-test/suite/innodb/r/analyze_table.result | 25 + .../suite/innodb/r/create_isl_with_direct.result | 1 + mysql-test/suite/innodb/r/dml_purge.result | 27 +- mysql-test/suite/innodb/r/foreign_key.result | 11 + .../suite/innodb/r/innodb-alter-nullable.result | 4 + .../suite/innodb/r/innodb-alter-timestamp.result | 12 +- mysql-test/suite/innodb/r/innodb-alter.result | 25 +- mysql-test/suite/innodb/r/innodb-isolation.result | 8 +- .../suite/innodb/r/innodb-online-alter-gis.result | 28 +- .../suite/innodb/r/innodb-table-online.result | 4 +- mysql-test/suite/innodb/r/innodb-wl5522.result | 14 + .../suite/innodb/r/innodb-wl5980-alter.result | 35 +- mysql-test/suite/innodb/r/innodb.result | 2 +- .../suite/innodb/r/innodb_bug27216817.result | 24 + mysql-test/suite/innodb/r/innodb_bug54044.result | 6 - mysql-test/suite/innodb/r/instant_alter.result | 20 +- .../suite/innodb/r/instant_alter_crash.result | 1 + .../suite/innodb/r/instant_alter_debug.result | 39 +- mysql-test/suite/innodb/r/log_file_name.result | 1 + .../suite/innodb/r/rename_table_debug.result | 1 + .../suite/innodb/r/row_format_redundant.result | 1 + mysql-test/suite/innodb/r/stored_fk.result | 74 + mysql-test/suite/innodb/r/temporary_table.result | 20 + mysql-test/suite/innodb/r/undo_log.result | 13 + .../suite/innodb/t/alter_algorithm.combinations | 11 + mysql-test/suite/innodb/t/alter_algorithm.inc | 2 + mysql-test/suite/innodb/t/alter_algorithm.test | 22 + mysql-test/suite/innodb/t/alter_foreign_crash.test | 37 + mysql-test/suite/innodb/t/alter_instant.test | 45 + mysql-test/suite/innodb/t/alter_kill-master.opt | 1 + mysql-test/suite/innodb/t/alter_kill.test | 158 + .../suite/innodb/t/alter_missing_tablespace.test | 22 +- mysql-test/suite/innodb/t/alter_not_null.test | 75 + .../suite/innodb/t/alter_not_null_debug.test | 68 + mysql-test/suite/innodb/t/alter_partitioned.test | 15 + .../suite/innodb/t/alter_partitioned_debug.test | 34 + .../suite/innodb/t/alter_partitioned_xa.test | 31 + mysql-test/suite/innodb/t/alter_rename_files.test | 31 + mysql-test/suite/innodb/t/analyze_table.test | 42 + mysql-test/suite/innodb/t/dml_purge.test | 18 +- mysql-test/suite/innodb/t/foreign_key.test | 12 + .../suite/innodb/t/innodb-alter-nullable.test | 5 + .../suite/innodb/t/innodb-alter-timestamp.test | 6 +- mysql-test/suite/innodb/t/innodb-alter.test | 22 + mysql-test/suite/innodb/t/innodb-isolation.test | 1 + mysql-test/suite/innodb/t/innodb-mdev7046.test | 3 - .../suite/innodb/t/innodb-online-alter-gis.test | 35 +- mysql-test/suite/innodb/t/innodb-table-online.test | 5 +- mysql-test/suite/innodb/t/innodb_bug13510739.test | 4 - mysql-test/suite/innodb/t/innodb_bug27216817.test | 28 + mysql-test/suite/innodb/t/innodb_bug54044.test | 10 - mysql-test/suite/innodb/t/instant_alter.test | 8 + mysql-test/suite/innodb/t/instant_alter_debug.test | 63 +- mysql-test/suite/innodb/t/rename_table_debug.test | 1 + mysql-test/suite/innodb/t/stored_fk.test | 94 + mysql-test/suite/innodb/t/temporary_table.test | 21 + mysql-test/suite/innodb/t/tmpdir.test | 5 - mysql-test/suite/innodb/t/undo_log.test | 14 + mysql-test/suite/innodb_fts/r/basic.result | 300 ++ mysql-test/suite/innodb_fts/r/create.result | 4 - mysql-test/suite/innodb_fts/r/fulltext.result | 2 - mysql-test/suite/innodb_fts/r/fulltext2.result | 1 - .../suite/innodb_fts/r/fulltext_table_evict.result | 19 + mysql-test/suite/innodb_fts/r/fulltext_var.result | 1 - .../suite/innodb_fts/r/innodb-fts-basic.result | 259 - .../suite/innodb_fts/r/innodb-fts-ddl.result | 21 +- .../suite/innodb_fts/r/innodb-fts-fic.result | 4 - .../suite/innodb_fts/r/innodb_fts_misc.result | 44 +- .../suite/innodb_fts/r/innodb_fts_misc_1.result | 4 - .../innodb_fts/r/innodb_fts_multiple_index.result | 2 - .../suite/innodb_fts/r/innodb_fts_plugin.result | 2 - .../suite/innodb_fts/r/innodb_fts_proximity.result | 10 - .../r/innodb_fts_result_cache_limit.result | 2 - .../innodb_fts/r/innodb_fts_transaction.result | 16 - mysql-test/suite/innodb_fts/t/basic.test | 265 + mysql-test/suite/innodb_fts/t/fulltext2.test | 8 - .../suite/innodb_fts/t/fulltext_table_evict.test | 48 + mysql-test/suite/innodb_fts/t/fulltext_var.test | 9 - .../suite/innodb_fts/t/innodb-fts-basic.test | 228 - mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test | 27 +- mysql-test/suite/innodb_fts/t/innodb_fts_misc.test | 1 + mysql-test/suite/innodb_gis/r/types.result | 1 + mysql-test/suite/innodb_gis/t/types.test | 3 + mysql-test/suite/innodb_zip/r/cmp_per_index.result | 19 +- mysql-test/suite/innodb_zip/r/restart.result | 8 + mysql-test/suite/innodb_zip/r/wl5522_zip.result | 13 + mysql-test/suite/innodb_zip/t/cmp_per_index.test | 4 +- mysql-test/suite/maria/alter.result | 16 + mysql-test/suite/maria/alter.test | 17 + mysql-test/suite/maria/lock.result | 35 + mysql-test/suite/maria/lock.test | 34 + mysql-test/suite/maria/maria-recover.result | 1 - .../suite/mariabackup/absolute_ibdata_paths.opt | 1 + .../suite/mariabackup/absolute_ibdata_paths.result | 10 + .../suite/mariabackup/absolute_ibdata_paths.test | 31 + mysql-test/suite/mariabackup/backup_ssl.result | 9 + mysql-test/suite/mariabackup/backup_ssl.test | 16 + .../suite/mariabackup/lock_ddl_per_table.opt | 1 + .../suite/mariabackup/lock_ddl_per_table.test | 1 + mysql-test/suite/mariabackup/partition_datadir.opt | 1 - .../suite/mariabackup/partition_datadir.test | 1 + mysql-test/suite/mariabackup/suite.opt | 2 +- mysql-test/suite/multi_source/info_logs.result | 12 +- mysql-test/suite/multi_source/multi_parallel.cnf | 6 + .../suite/multi_source/multi_parallel.result | 64 + mysql-test/suite/multi_source/multi_parallel.test | 125 + .../suite/multi_source/multi_parallel_loop.inc | 19 + mysql-test/suite/multi_source/reset_slave.result | 8 +- mysql-test/suite/multi_source/simple.result | 23 +- mysql-test/suite/multi_source/syntax.result | 6 +- mysql-test/suite/parts/inc/part_alter_values.inc | 10 + .../suite/parts/r/partition_alter_innodb.result | 6 + .../suite/parts/r/partition_alter_maria.result | 6 + .../suite/parts/r/partition_alter_myisam.result | 6 + .../parts/r/partition_basic_symlink_innodb.result | 3 + mysql-test/suite/parts/r/partition_debug.result | 45 + .../suite/parts/r/partition_debug_innodb.result | 262 + .../suite/parts/r/partition_debug_myisam.result | 217 + .../parts/r/partition_debug_sync_innodb.result | 3 + .../suite/parts/r/partition_recover_myisam.result | 2 - mysql-test/suite/parts/r/quoting.result | 93 - mysql-test/suite/parts/r/show_create.result | 105 + mysql-test/suite/parts/t/quoting.test | 32 - mysql-test/suite/parts/t/show_create.test | 41 + mysql-test/suite/perfschema/r/partition.result | 10 + mysql-test/suite/perfschema/t/partition.test | 16 + mysql-test/suite/plugins/r/server_audit.result | 12 + mysql-test/suite/plugins/t/server_audit.test | 7 + mysql-test/suite/rpl/disabled.def | 2 + .../rpl/include/rpl_implicit_commit_binlog.test | 430 +- mysql-test/suite/rpl/r/rename.result | 36 + mysql-test/suite/rpl/r/rpl_mdev12179.result | 22 +- .../rpl/r/rpl_mixed_implicit_commit_binlog.result | 534 +- .../rpl/r/rpl_row_implicit_commit_binlog.result | 528 +- .../rpl/r/rpl_stm_implicit_commit_binlog.result | 518 +- mysql-test/suite/rpl/t/rename.test | 33 + mysql-test/suite/rpl/t/rpl_mdev12179.test | 40 +- mysql-test/suite/rpl/t/rpl_mdev382.test | 5 - mysql-test/suite/rpl/t/rpl_row_triggers.test | 1 - mysql-test/suite/sql_sequence/debug_sync.opt | 1 + mysql-test/suite/sql_sequence/debug_sync.result | 7 + mysql-test/suite/sql_sequence/debug_sync.test | 15 + mysql-test/suite/sql_sequence/lock.result | 10 + mysql-test/suite/sql_sequence/lock.test | 24 + mysql-test/suite/sql_sequence/other.result | 34 +- mysql-test/suite/sql_sequence/other.test | 31 +- .../suite/sql_sequence/replication_drop.result | 5 + .../suite/sql_sequence/replication_drop.test | 17 + .../suite/sql_sequence/replication_mixed.result | 35 + .../suite/sql_sequence/replication_mixed.test | 27 + mysql-test/suite/sql_sequence/setval.result | 15 + mysql-test/suite/sql_sequence/setval.test | 17 + mysql-test/suite/sql_sequence/temporary.result | 24 + mysql-test/suite/sql_sequence/temporary.test | 18 + .../suite/storage_engine/parts/repair_table.result | 5 +- .../suite/storage_engine/repair_table.result | 1 + .../suite/sys_vars/inc/secure_timestamp_func.inc | 49 + .../sys_vars/r/collation_database_func.result | 2 +- .../r/innodb_change_buffering_basic.result | 6 +- .../sys_vars/r/innodb_flush_method_basic.result | 24 +- .../sys_vars/r/innodb_flush_method_func.result | 15 + .../suite/sys_vars/r/old_alter_table_basic.result | 90 +- .../suite/sys_vars/r/secure_timestamp_no.result | 40 + .../suite/sys_vars/r/secure_timestamp_rpl.result | 42 + .../suite/sys_vars/r/secure_timestamp_super.result | 41 + .../suite/sys_vars/r/secure_timestamp_yes.result | 42 + .../r/slave_run_triggers_for_rbr_basic.result | 45 - .../suite/sys_vars/r/sysvars_innodb,32bit.rdiff | 17 +- mysql-test/suite/sys_vars/r/sysvars_innodb.result | 41 +- .../sys_vars/r/sysvars_server_embedded.result | 42 +- .../sys_vars/r/sysvars_server_notembedded.result | 42 +- .../sys_vars/t/innodb_change_buffering_basic.test | 4 +- .../sys_vars/t/innodb_flush_method_basic.test | 10 +- .../suite/sys_vars/t/innodb_flush_method_func.test | 26 + .../innodb_stats_include_delete_marked_basic.test | 5 - .../suite/sys_vars/t/old_alter_table_basic.test | 28 +- .../suite/sys_vars/t/secure_timestamp_no-slave.opt | 1 + .../suite/sys_vars/t/secure_timestamp_no.test | 4 + .../sys_vars/t/secure_timestamp_rpl-slave.opt | 1 + .../suite/sys_vars/t/secure_timestamp_rpl.test | 4 + .../sys_vars/t/secure_timestamp_super-slave.opt | 1 + .../suite/sys_vars/t/secure_timestamp_super.test | 4 + .../sys_vars/t/secure_timestamp_yes-slave.opt | 1 + .../suite/sys_vars/t/secure_timestamp_yes.test | 4 + .../t/slave_run_triggers_for_rbr_basic.test | 30 - mysql-test/suite/sys_vars/t/sysvars_innodb.test | 6 +- mysql-test/suite/vcol/r/binlog.result | 70 + mysql-test/suite/vcol/r/partition.result | 10 + mysql-test/suite/vcol/r/update.result | 12 + mysql-test/suite/vcol/r/update_binlog.result | 361 ++ mysql-test/suite/vcol/t/binlog.test | 55 + mysql-test/suite/vcol/t/partition.test | 12 + mysql-test/suite/vcol/t/update.test | 2 + mysql-test/suite/vcol/t/update_binlog.test | 14 + mysql-test/suite/versioning/common.inc | 4 +- mysql-test/suite/versioning/common_finish.inc | 4 +- mysql-test/suite/versioning/disabled.def | 1 - mysql-test/suite/versioning/r/alter.result | 22 +- mysql-test/suite/versioning/r/commit_id.result | 52 +- mysql-test/suite/versioning/r/cte.result | 226 +- mysql-test/suite/versioning/r/foreign,trx_id.rdiff | 166 - mysql-test/suite/versioning/r/insert2.result | 18 - mysql-test/suite/versioning/r/partition.result | 22 + .../suite/versioning/r/partition_rotation.result | 58 + mysql-test/suite/versioning/r/rpl_stmt.result | 61 - mysql-test/suite/versioning/r/select.result | 30 +- mysql-test/suite/versioning/r/select2,trx_id.rdiff | 2 +- mysql-test/suite/versioning/r/select2.result | 8 + mysql-test/suite/versioning/r/sysvars.result | 3 + mysql-test/suite/versioning/r/truncate.result | 24 +- mysql-test/suite/versioning/r/trx_id.result | 224 + mysql-test/suite/versioning/t/alter.test | 31 +- mysql-test/suite/versioning/t/commit_id.test | 52 +- mysql-test/suite/versioning/t/cte.test | 153 +- mysql-test/suite/versioning/t/insert2.test | 20 +- mysql-test/suite/versioning/t/partition.test | 24 + .../suite/versioning/t/partition_rotation.test | 40 + mysql-test/suite/versioning/t/rpl_stmt.test | 53 - mysql-test/suite/versioning/t/select.test | 26 +- mysql-test/suite/versioning/t/select2.test | 10 +- mysql-test/suite/versioning/t/sysvars.test | 2 + mysql-test/suite/versioning/t/truncate.test | 29 +- mysql-test/suite/versioning/t/trx_id.opt | 1 + mysql-test/suite/versioning/t/trx_id.test | 268 + mysql-test/t/ps_qc_innodb.test | 35 - mysql-test/unstable-tests | 655 +-- mysys/CMakeLists.txt | 4 +- mysys/file_logger.c | 2 +- mysys/hash.c | 2 +- mysys/lf_hash.c | 8 +- mysys/mf_cache.c | 37 +- mysys/mf_tempfile.c | 133 +- mysys/my_default.c | 10 +- mysys/my_fopen.c | 38 +- mysys/my_init.c | 32 +- mysys/my_likely.c | 173 + mysys/my_open.c | 49 +- mysys/my_winfile.c | 4 +- mysys/psi_noop.c | 7 + mysys/ptr_cmp.c | 36 +- mysys_ssl/openssl.c | 4 +- pcre/AUTHORS | 6 +- pcre/ChangeLog | 53 + pcre/INSTALL | 320 +- pcre/LICENCE | 6 +- pcre/NEWS | 6 + pcre/NON-AUTOTOOLS-BUILD | 15 +- pcre/configure.ac | 26 +- pcre/doc/html/NON-AUTOTOOLS-BUILD.txt | 15 +- pcre/pcre.h.in | 8 +- pcre/pcre_compile.c | 2 +- pcre/pcre_dfa_exec.c | 4 +- pcre/pcre_exec.c | 8 +- pcre/pcre_jit_compile.c | 407 +- pcre/pcregrep.c | 55 +- pcre/pcreposix.c | 6 +- pcre/testdata/testinput2 | 8 + pcre/testdata/testinput5 | 6 + pcre/testdata/testoutput2 | 16 + pcre/testdata/testoutput5 | 8 + plugin/server_audit/server_audit.c | 29 +- plugin/versioning/versioning.cc | 58 +- scripts/CMakeLists.txt | 1 + scripts/make_win_bin_dist | 416 -- scripts/mysql_install_db.pl.in | 659 --- scripts/mysql_install_db.sh | 47 +- scripts/mysql_system_tables_data.sql | 17 +- scripts/mysql_test_db.sql | 31 + scripts/mysqld_safe.sh | 12 +- scripts/wsrep_sst_xtrabackup.sh | 1 - sql-bench/server-cfg.sh | 1 + sql-bench/test-ATIS.sh | 2 +- sql-bench/test-alter-table.sh | 36 +- sql-bench/test-insert.sh | 13 +- sql-common/client.c | 33 +- sql-common/client_plugin.c | 2 +- sql/CMakeLists.txt | 3 +- sql/create_options.h | 2 +- sql/datadict.cc | 4 +- sql/debug_sync.cc | 4 +- sql/derror.cc | 7 +- sql/discover.cc | 2 +- sql/event_data_objects.cc | 2 +- sql/events.cc | 10 +- sql/field.cc | 181 +- sql/field.h | 190 +- sql/field_comp.cc | 22 +- sql/field_conv.cc | 9 +- sql/filesort.cc | 173 +- sql/gen_lex_hash.cc | 37 +- sql/group_by_handler.cc | 7 +- sql/ha_partition.cc | 472 +- sql/ha_sequence.cc | 33 +- sql/handler.cc | 147 +- sql/handler.h | 99 +- sql/hostname.cc | 2 +- sql/item.cc | 174 +- sql/item.h | 119 +- sql/item_cmpfunc.cc | 72 +- sql/item_create.cc | 118 +- sql/item_func.cc | 64 +- sql/item_func.h | 33 +- sql/item_inetfunc.cc | 16 +- sql/item_jsonfunc.cc | 50 +- sql/item_row.cc | 2 +- sql/item_strfunc.cc | 38 +- sql/item_strfunc.h | 23 +- sql/item_subselect.cc | 65 +- sql/item_sum.cc | 29 +- sql/item_sum.h | 6 + sql/item_timefunc.cc | 19 +- sql/item_vers.cc | 34 +- sql/item_vers.h | 50 +- sql/item_xmlfunc.cc | 4 + sql/key.cc | 6 +- sql/lex.h | 2 +- sql/lex_string.h | 14 +- sql/lock.cc | 10 +- sql/log.cc | 158 +- sql/log.h | 1 + sql/log_event.cc | 292 +- sql/log_event.h | 3 +- sql/log_event_old.cc | 140 +- sql/mdl.h | 4 +- sql/mf_iocache.cc | 7 +- sql/mf_iocache_encr.cc | 4 +- sql/multi_range_read.cc | 6 - sql/mysql_install_db.cc | 1 - sql/mysqld.cc | 72 +- sql/mysqld.h | 6 +- sql/net_serv.cc | 12 +- sql/opt_range.cc | 242 +- sql/opt_range_mrr.cc | 4 +- sql/opt_split.cc | 16 +- sql/opt_subselect.cc | 38 +- sql/opt_sum.cc | 11 +- sql/parse_file.cc | 3 +- sql/partition_info.cc | 86 +- sql/password.c | 4 +- sql/plistsort.c | 10 +- sql/protocol.cc | 10 +- sql/records.cc | 42 +- sql/records.h | 2 +- sql/rpl_gtid.cc | 7 +- sql/rpl_injector.cc | 8 +- sql/rpl_mi.cc | 11 +- sql/rpl_mi.h | 10 + sql/rpl_parallel.cc | 30 +- sql/rpl_record.cc | 3 +- sql/rpl_rli.cc | 24 +- sql/rpl_utility.cc | 16 + sql/semisync_master.cc | 12 +- sql/set_var.cc | 10 +- sql/share/errmsg-utf8.txt | 12 +- sql/slave.cc | 132 +- sql/sp.cc | 72 +- sql/sp.h | 8 +- sql/sp_head.cc | 64 +- sql/sp_head.h | 2 +- sql/sp_rcontext.cc | 17 +- sql/spatial.cc | 6 +- sql/sql_acl.cc | 140 +- sql/sql_acl.h | 2 +- sql/sql_admin.cc | 26 +- sql/sql_alloc.h | 2 +- sql/sql_alter.cc | 152 +- sql/sql_alter.h | 57 +- sql/sql_array.h | 2 +- sql/sql_base.cc | 194 +- sql/sql_base.h | 14 +- sql/sql_bitmap.h | 15 +- sql/sql_cache.cc | 21 +- sql/sql_cache.h | 1 + sql/sql_class.cc | 158 +- sql/sql_class.h | 279 +- sql/sql_connect.cc | 26 +- sql/sql_cte.cc | 21 +- sql/sql_db.cc | 28 +- sql/sql_delete.cc | 119 +- sql/sql_derived.cc | 9 +- sql/sql_do.cc | 2 +- sql/sql_error.cc | 4 +- sql/sql_expression_cache.cc | 5 +- sql/sql_get_diagnostics.cc | 4 +- sql/sql_handler.cc | 68 +- sql/sql_handler.h | 3 +- sql/sql_help.cc | 7 +- sql/sql_insert.cc | 140 +- sql/sql_join_cache.cc | 12 +- sql/sql_lex.cc | 2055 +++++--- sql/sql_lex.h | 500 +- sql/sql_lifo_buffer.h | 4 +- sql/sql_list.h | 23 +- sql/sql_load.cc | 31 +- sql/sql_parse.cc | 309 +- sql/sql_parse.h | 2 +- sql/sql_partition.cc | 179 +- sql/sql_partition.h | 1 - sql/sql_partition_admin.cc | 119 +- sql/sql_plist.h | 8 +- sql/sql_plugin.cc | 127 +- sql/sql_prepare.cc | 99 +- sql/sql_priv.h | 4 +- sql/sql_reload.cc | 8 +- sql/sql_rename.cc | 10 +- sql/sql_repl.cc | 78 +- sql/sql_select.cc | 633 ++- sql/sql_select.h | 17 +- sql/sql_sequence.cc | 27 +- sql/sql_servers.cc | 62 +- sql/sql_show.cc | 133 +- sql/sql_signal.cc | 2 +- sql/sql_sort.h | 12 +- sql/sql_statistics.cc | 27 +- sql/sql_string.cc | 25 +- sql/sql_string.h | 24 +- sql/sql_table.cc | 434 +- sql/sql_tablespace.cc | 9 +- sql/sql_trigger.cc | 39 +- sql/sql_truncate.cc | 18 +- sql/sql_tvc.cc | 8 +- sql/sql_type.cc | 92 +- sql/sql_type.h | 245 +- sql/sql_udf.cc | 14 +- sql/sql_union.cc | 202 +- sql/sql_update.cc | 283 +- sql/sql_view.cc | 26 +- sql/sql_window.cc | 4 +- sql/sql_yacc.yy | 4999 +++++++++--------- sql/sql_yacc_ora.yy | 5479 +++++++++++--------- sql/strfunc.cc | 3 +- sql/structs.h | 6 - sql/sys_vars.cc | 92 +- sql/sys_vars.ic | 13 +- sql/table.cc | 293 +- sql/table.h | 50 +- sql/table_cache.cc | 8 +- sql/temporary_tables.cc | 21 +- sql/thr_malloc.cc | 2 +- sql/threadpool_common.cc | 2 +- sql/threadpool_win.cc | 4 +- sql/transaction.cc | 3 +- sql/tztime.cc | 5 +- sql/uniques.cc | 12 +- sql/uniques.h | 2 +- sql/unireg.cc | 6 +- sql/vers_string.h | 134 +- sql/wsrep_applier.cc | 8 +- sql/wsrep_binlog.cc | 2 +- sql/wsrep_hton.cc | 44 +- sql/wsrep_mysqld.cc | 35 +- sql/wsrep_mysqld.h | 2 - sql/wsrep_thd.cc | 38 +- storage/archive/ha_archive.cc | 12 +- storage/connect/filamtxt.cpp | 2 +- storage/connect/ha_connect.cc | 13 +- storage/connect/jsonudf.cpp | 4 +- storage/connect/mycat.cc | 7 +- storage/connect/mysql-test/connect/r/grant.result | 2 +- storage/connect/mysql-test/connect/t/grant.test | 2 +- storage/connect/plugutil.cpp | 2 +- storage/connect/tabext.cpp | 2 +- storage/connect/tabjdbc.cpp | 2 +- storage/connect/tabjson.cpp | 14 +- storage/federatedx/federatedx_io_mysql.cc | 3 +- storage/federatedx/ha_federatedx.cc | 39 +- storage/heap/heapdef.h | 2 +- storage/heap/hp_hash.c | 2 - storage/innobase/CMakeLists.txt | 3 +- storage/innobase/btr/btr0btr.cc | 114 +- storage/innobase/btr/btr0bulk.cc | 29 +- storage/innobase/btr/btr0cur.cc | 217 +- storage/innobase/btr/btr0defragment.cc | 8 +- storage/innobase/btr/btr0pcur.cc | 21 +- storage/innobase/btr/btr0scrub.cc | 6 +- storage/innobase/btr/btr0sea.cc | 64 +- storage/innobase/buf/buf0buddy.cc | 38 +- storage/innobase/buf/buf0buf.cc | 129 +- storage/innobase/buf/buf0checksum.cc | 6 +- storage/innobase/buf/buf0dblwr.cc | 65 +- storage/innobase/buf/buf0dump.cc | 6 +- storage/innobase/buf/buf0flu.cc | 27 +- storage/innobase/buf/buf0lru.cc | 89 +- storage/innobase/buf/buf0rea.cc | 10 +- storage/innobase/data/data0data.cc | 8 +- storage/innobase/dict/dict0boot.cc | 8 +- storage/innobase/dict/dict0crea.cc | 28 +- storage/innobase/dict/dict0defrag_bg.cc | 4 +- storage/innobase/dict/dict0dict.cc | 253 +- storage/innobase/dict/dict0load.cc | 48 +- storage/innobase/dict/dict0mem.cc | 41 +- storage/innobase/dict/dict0stats.cc | 24 +- storage/innobase/dict/dict0stats_bg.cc | 18 +- storage/innobase/eval/eval0eval.cc | 9 +- storage/innobase/fil/fil0crypt.cc | 69 +- storage/innobase/fil/fil0fil.cc | 225 +- storage/innobase/fil/fil0pagecompress.cc | 50 +- storage/innobase/fsp/fsp0file.cc | 39 +- storage/innobase/fsp/fsp0fsp.cc | 4 +- storage/innobase/fsp/fsp0sysspace.cc | 21 +- storage/innobase/fts/fts0config.cc | 2 +- storage/innobase/fts/fts0fts.cc | 31 +- storage/innobase/fts/fts0opt.cc | 100 +- storage/innobase/fts/fts0plugin.cc | 22 +- storage/innobase/fts/fts0que.cc | 14 +- storage/innobase/fts/fts0sql.cc | 6 +- storage/innobase/gis/gis0geo.cc | 44 +- storage/innobase/gis/gis0rtree.cc | 141 +- storage/innobase/gis/gis0sea.cc | 9 +- storage/innobase/ha/ha0ha.cc | 20 +- storage/innobase/handler/ha_innodb.cc | 1548 +++--- storage/innobase/handler/ha_innodb.h | 45 +- storage/innobase/handler/handler0alter.cc | 820 +-- storage/innobase/handler/i_s.cc | 227 +- storage/innobase/ibuf/ibuf0ibuf.cc | 91 +- storage/innobase/include/btr0btr.h | 38 +- storage/innobase/include/btr0bulk.h | 5 +- storage/innobase/include/btr0cur.h | 13 +- storage/innobase/include/btr0cur.ic | 5 +- storage/innobase/include/btr0pcur.h | 14 +- storage/innobase/include/btr0pcur.ic | 24 +- storage/innobase/include/btr0sea.h | 74 +- storage/innobase/include/btr0sea.ic | 41 +- storage/innobase/include/buf0buddy.h | 10 +- storage/innobase/include/buf0buddy.ic | 16 +- storage/innobase/include/buf0buf.h | 8 +- storage/innobase/include/buf0buf.ic | 25 +- storage/innobase/include/buf0checksum.h | 2 +- storage/innobase/include/buf0dblwr.h | 4 +- storage/innobase/include/buf0flu.h | 12 +- storage/innobase/include/buf0lru.h | 18 +- storage/innobase/include/buf0types.h | 4 +- storage/innobase/include/data0type.h | 4 +- storage/innobase/include/data0type.ic | 28 +- storage/innobase/include/dict0boot.ic | 10 +- storage/innobase/include/dict0dict.h | 37 +- storage/innobase/include/dict0dict.ic | 71 +- storage/innobase/include/dict0load.h | 1 - storage/innobase/include/dict0mem.h | 33 +- storage/innobase/include/dict0stats_bg.h | 13 +- storage/innobase/include/dict0types.h | 3 +- storage/innobase/include/dyn0buf.h | 61 +- storage/innobase/include/fil0crypt.h | 4 +- storage/innobase/include/fil0fil.h | 86 +- storage/innobase/include/fsp0file.h | 11 +- storage/innobase/include/fsp0fsp.h | 6 +- storage/innobase/include/fsp0fsp.ic | 24 +- storage/innobase/include/fsp0sysspace.h | 6 +- storage/innobase/include/fsp0types.h | 29 +- storage/innobase/include/fts0fts.h | 3 - storage/innobase/include/fts0priv.h | 1 - storage/innobase/include/fts0tokenize.h | 2 +- storage/innobase/include/fts0types.ic | 7 +- storage/innobase/include/fut0fut.ic | 2 +- storage/innobase/include/fut0lst.ic | 4 +- storage/innobase/include/gis0rtree.h | 22 +- storage/innobase/include/ha_prototypes.h | 27 +- storage/innobase/include/ib0mutex.h | 77 +- storage/innobase/include/ibuf0ibuf.h | 13 +- storage/innobase/include/ibuf0ibuf.ic | 6 +- storage/innobase/include/lock0prdt.h | 5 +- storage/innobase/include/log0log.h | 299 +- storage/innobase/include/log0log.ic | 56 +- storage/innobase/include/log0recv.h | 22 +- storage/innobase/include/mem0mem.h | 11 +- storage/innobase/include/mem0mem.ic | 5 +- storage/innobase/include/mtr0log.ic | 2 +- storage/innobase/include/mtr0mtr.h | 16 +- storage/innobase/include/mtr0types.h | 4 +- storage/innobase/include/os0event.h | 6 +- storage/innobase/include/os0file.h | 21 +- storage/innobase/include/os0file.ic | 88 - storage/innobase/include/os0thread.h | 6 - storage/innobase/include/page0cur.h | 6 +- storage/innobase/include/page0cur.ic | 7 +- storage/innobase/include/page0page.h | 18 +- storage/innobase/include/page0page.ic | 24 +- storage/innobase/include/page0size.h | 2 +- storage/innobase/include/page0zip.ic | 8 +- storage/innobase/include/pars0pars.h | 2 +- storage/innobase/include/rem0rec.h | 20 +- storage/innobase/include/rem0rec.ic | 83 +- storage/innobase/include/row0log.h | 7 +- storage/innobase/include/row0merge.h | 28 +- storage/innobase/include/row0mysql.h | 2 +- storage/innobase/include/row0row.h | 11 +- storage/innobase/include/row0sel.h | 3 +- storage/innobase/include/row0upd.h | 4 +- storage/innobase/include/row0upd.ic | 5 +- storage/innobase/include/row0vers.h | 18 +- storage/innobase/include/srv0conc.h | 5 +- storage/innobase/include/srv0mon.h | 20 +- storage/innobase/include/srv0srv.h | 50 +- storage/innobase/include/srv0start.h | 14 +- storage/innobase/include/sync0arr.h | 15 +- storage/innobase/include/sync0policy.h | 24 +- storage/innobase/include/sync0policy.ic | 2 +- storage/innobase/include/sync0rw.ic | 10 +- storage/innobase/include/sync0sync.h | 1 - storage/innobase/include/sync0types.h | 75 +- storage/innobase/include/trx0purge.h | 105 +- storage/innobase/include/trx0rec.h | 2 +- storage/innobase/include/trx0rec.ic | 4 +- storage/innobase/include/trx0roll.h | 2 - storage/innobase/include/trx0roll.ic | 62 - storage/innobase/include/trx0rseg.h | 2 +- storage/innobase/include/trx0sys.h | 33 +- storage/innobase/include/trx0trx.h | 18 +- storage/innobase/include/trx0types.h | 1 - storage/innobase/include/trx0undo.h | 31 +- storage/innobase/include/trx0undo.ic | 34 +- storage/innobase/include/univ.i | 47 +- storage/innobase/include/ut0byte.ic | 6 - storage/innobase/include/ut0lst.h | 2 +- storage/innobase/include/ut0new.h | 63 +- storage/innobase/include/ut0pool.h | 22 +- storage/innobase/include/ut0stage.h | 61 +- storage/innobase/innodb.cmake | 21 +- storage/innobase/lock/lock0lock.cc | 91 +- storage/innobase/lock/lock0prdt.cc | 20 +- storage/innobase/lock/lock0wait.cc | 48 +- storage/innobase/log/log0crypt.cc | 69 +- storage/innobase/log/log0log.cc | 955 ++-- storage/innobase/log/log0recv.cc | 279 +- storage/innobase/mem/mem0mem.cc | 10 +- storage/innobase/mtr/mtr0log.cc | 76 +- storage/innobase/mtr/mtr0mtr.cc | 19 +- .../mysql-test/storage_engine/repair_table.rdiff | 5 +- storage/innobase/os/os0event.cc | 13 +- storage/innobase/os/os0file.cc | 96 +- storage/innobase/os/os0thread.cc | 4 +- storage/innobase/page/page0cur.cc | 57 +- storage/innobase/page/page0page.cc | 119 +- storage/innobase/page/page0zip.cc | 237 +- storage/innobase/pars/pars0opt.cc | 5 +- storage/innobase/pars/pars0pars.cc | 13 +- storage/innobase/que/que0que.cc | 7 +- storage/innobase/rem/rem0cmp.cc | 12 +- storage/innobase/rem/rem0rec.cc | 180 +- storage/innobase/row/row0ftsort.cc | 48 +- storage/innobase/row/row0import.cc | 85 +- storage/innobase/row/row0ins.cc | 42 +- storage/innobase/row/row0log.cc | 361 +- storage/innobase/row/row0merge.cc | 262 +- storage/innobase/row/row0mysql.cc | 65 +- storage/innobase/row/row0purge.cc | 17 +- storage/innobase/row/row0quiesce.cc | 10 +- storage/innobase/row/row0row.cc | 36 +- storage/innobase/row/row0sel.cc | 120 +- storage/innobase/row/row0trunc.cc | 85 +- storage/innobase/row/row0uins.cc | 2 +- storage/innobase/row/row0umod.cc | 278 +- storage/innobase/row/row0upd.cc | 41 +- storage/innobase/row/row0vers.cc | 37 +- storage/innobase/srv/srv0conc.cc | 27 +- storage/innobase/srv/srv0mon.cc | 16 +- storage/innobase/srv/srv0srv.cc | 170 +- storage/innobase/srv/srv0start.cc | 296 +- storage/innobase/sync/sync0arr.cc | 26 +- storage/innobase/sync/sync0debug.cc | 19 +- storage/innobase/sync/sync0rw.cc | 22 +- storage/innobase/sync/sync0sync.cc | 1 - storage/innobase/trx/trx0purge.cc | 231 +- storage/innobase/trx/trx0rec.cc | 328 +- storage/innobase/trx/trx0roll.cc | 93 +- storage/innobase/trx/trx0rseg.cc | 2 +- storage/innobase/trx/trx0sys.cc | 6 +- storage/innobase/trx/trx0trx.cc | 33 +- storage/innobase/trx/trx0undo.cc | 147 +- storage/innobase/ut/ut0ut.cc | 34 +- storage/maria/ha_maria.cc | 143 +- storage/maria/ha_maria.h | 1 + storage/maria/ma_blockrec.c | 3 +- storage/maria/ma_blockrec.h | 3 +- storage/maria/ma_close.c | 2 + storage/maria/ma_commit.c | 17 +- storage/maria/ma_create.c | 4 +- storage/maria/ma_delete_all.c | 3 + storage/maria/ma_extra.c | 4 +- storage/maria/ma_ft_nlq_search.c | 10 +- storage/maria/ma_ft_update.c | 11 +- storage/maria/ma_fulltext.h | 5 + storage/maria/ma_key.c | 2 - storage/maria/ma_open.c | 4 +- storage/maria/ma_pagecache.c | 2 +- storage/maria/ma_recovery.c | 11 + storage/maria/ma_sp_key.c | 2 - storage/maria/ma_state.c | 48 +- storage/maria/ma_state.h | 2 +- storage/maria/ma_trnman.h | 65 + storage/maria/maria_def.h | 26 +- storage/maria/trnman.c | 1 + storage/maria/trnman.h | 3 +- storage/mroonga/CMakeLists.txt | 3 + storage/mroonga/vendor/groonga/CMakeLists.txt | 14 +- storage/mroonga/vendor/groonga/lib/CMakeLists.txt | 3 +- storage/mroonga/vendor/groonga/lib/alloc.c | 2 +- storage/mroonga/vendor/groonga/lib/grn.h | 2 +- storage/myisam/ft_nlq_search.c | 8 - storage/myisam/ft_update.c | 11 +- storage/myisam/fulltext.h | 5 + storage/myisam/ha_myisam.cc | 2 +- storage/myisam/mi_delete_all.c | 4 + storage/myisam/mi_key.c | 2 - storage/myisam/myisamdef.h | 7 +- .../storage_engine/trx/xa_recovery.rdiff | 3 +- storage/myisam/sp_key.c | 2 - storage/myisammrg/ha_myisammrg.cc | 2 +- .../storage_engine/parts/repair_table.rdiff | 11 +- .../mysql-test/storage_engine/repair_table.rdiff | 5 +- .../storage_engine/trx/xa_recovery.rdiff | 3 +- storage/oqgraph/graphcore-config.h | 2 + storage/oqgraph/oqgraph_shim.h | 3 - storage/perfschema/ha_perfschema.cc | 2 +- storage/perfschema/pfs.cc | 86 +- storage/perfschema/pfs_global.h | 2 +- storage/perfschema/pfs_instr_class.cc | 7 - storage/rocksdb/CMakeLists.txt | 22 +- storage/rocksdb/build_rocksdb.cmake | 9 +- storage/rocksdb/event_listener.cc | 10 + storage/rocksdb/event_listener.h | 3 + storage/rocksdb/ha_rocksdb.cc | 2165 +++++--- storage/rocksdb/ha_rocksdb.h | 119 +- .../rocksdb/include/autoinc_crash_safe.inc | 150 + .../mysql-test/rocksdb/include/bulk_load.inc | 153 + .../rocksdb/include/bulk_load_unsorted.inc | 142 + .../include/restart_mysqld_with_invalid_option.inc | 8 + .../rocksdb/include/start_mysqld_with_option.inc | 14 + .../r/add_index_inplace_sstfilewriter.result | 7 + .../rocksdb/r/allow_no_primary_key.result | 31 + .../rocksdb/r/allow_no_primary_key_with_sk.result | 17 + .../r/allow_to_start_after_corruption.result | 38 + .../mysql-test/rocksdb/r/analyze_table.result | 26 + .../mysql-test/rocksdb/r/autoinc_crash_safe.result | 132 + .../rocksdb/r/autoinc_crash_safe_partition.result | 132 + .../mysql-test/rocksdb/r/autoinc_debug.result | 107 + .../mysql-test/rocksdb/r/autoinc_vars.result | 89 + .../mysql-test/rocksdb/r/autoincrement.result | 1 - .../mysql-test/rocksdb/r/bloomfilter.result | 5 + .../mysql-test/rocksdb/r/bloomfilter5.result | 62 + .../mysql-test/rocksdb/r/bloomfilter_skip.result | 5 + .../rocksdb/mysql-test/rocksdb/r/bulk_load.result | 58 +- .../rocksdb/r/bulk_load_drop_table.result | 11 + .../mysql-test/rocksdb/r/bulk_load_errors.result | 64 +- .../mysql-test/rocksdb/r/bulk_load_rev_cf.result | 58 +- .../rocksdb/r/bulk_load_rev_cf_and_data.result | 58 +- .../mysql-test/rocksdb/r/bulk_load_rev_data.result | 58 +- .../mysql-test/rocksdb/r/bulk_load_unsorted.result | 98 +- .../rocksdb/r/bulk_load_unsorted_rev.result | 111 + .../mysql-test/rocksdb/r/cardinality.result | 35 + .../rocksdb/r/check_ignore_unknown_options.result | 7 + .../mysql-test/rocksdb/r/deadlock_tracking.result | 67 +- .../rocksdb/mysql-test/rocksdb/r/i_s_ddl.result | 19 +- .../mysql-test/rocksdb/r/i_s_deadlock.result | 215 + .../rocksdb/r/index_merge_rocksdb.result | 2 +- .../mysql-test/rocksdb/r/information_schema.result | 7 +- .../rocksdb/mysql-test/rocksdb/r/issue255.result | 47 + .../rocksdb/r/lock_wait_timeout_stats.result | 8 + .../mysql-test/rocksdb/r/mariadb_port_fixes.result | 43 +- .../mysql-test/rocksdb/r/max_open_files.result | 21 + .../rocksdb/r/optimizer_loose_index_scans.result | 42 +- .../mysql-test/rocksdb/r/perf_context.result | 32 +- .../rocksdb/mysql-test/rocksdb/r/rocksdb.result | 90 +- .../mysql-test/rocksdb/r/rocksdb_debug.result | 11 + .../mysql-test/rocksdb/r/rocksdb_range2.result | 2 +- .../mysql-test/rocksdb/r/show_engine.result | 36 +- .../rocksdb/r/skip_validate_tmp_table.result | 18 +- .../mysql-test/rocksdb/r/transaction.result | 24 + .../rocksdb/r/ttl_primary_read_filtering.result | 28 + .../mysql-test/rocksdb/r/type_varchar.result | 14 +- .../rocksdb/r/use_direct_reads_writes.result | 32 +- .../rocksdb/mysql-test/rocksdb/r/write_sync.result | 5 +- .../rocksdb/t/add_index_inplace_sstfilewriter.test | 8 + .../mysql-test/rocksdb/t/allow_no_primary_key.test | 28 + .../rocksdb/t/allow_no_primary_key_with_sk.test | 12 + .../rocksdb/t/allow_to_start_after_corruption.test | 75 + .../mysql-test/rocksdb/t/analyze_table.test | 26 + .../mysql-test/rocksdb/t/autoinc_crash_safe.cnf | 8 + .../mysql-test/rocksdb/t/autoinc_crash_safe.test | 9 + .../rocksdb/t/autoinc_crash_safe_partition.cnf | 8 + .../rocksdb/t/autoinc_crash_safe_partition.test | 10 + .../mysql-test/rocksdb/t/autoinc_debug-master.opt | 1 + .../mysql-test/rocksdb/t/autoinc_debug.test | 118 + .../rocksdb/mysql-test/rocksdb/t/autoinc_vars.test | 51 + .../mysql-test/rocksdb/t/autoincrement.test | 3 - .../mysql-test/rocksdb/t/bloomfilter5-master.opt | 1 + .../rocksdb/mysql-test/rocksdb/t/bloomfilter5.test | 61 + .../rocksdb/t/bloomfilter_load_select.inc | 1 + storage/rocksdb/mysql-test/rocksdb/t/bulk_load.inc | 156 - .../rocksdb/mysql-test/rocksdb/t/bulk_load.test | 2 +- .../mysql-test/rocksdb/t/bulk_load_drop_table.test | 19 + .../mysql-test/rocksdb/t/bulk_load_errors.test | 97 +- .../mysql-test/rocksdb/t/bulk_load_rev_cf.test | 2 +- .../rocksdb/t/bulk_load_rev_cf_and_data.test | 2 +- .../mysql-test/rocksdb/t/bulk_load_rev_data.test | 2 +- .../mysql-test/rocksdb/t/bulk_load_unsorted.test | 134 +- .../rocksdb/t/bulk_load_unsorted_rev.test | 5 + .../rocksdb/mysql-test/rocksdb/t/cardinality.test | 42 + .../rocksdb/t/check_ignore_unknown_options.test | 26 + .../mysql-test/rocksdb/t/deadlock_tracking.test | 20 +- storage/rocksdb/mysql-test/rocksdb/t/disabled.def | 1 - storage/rocksdb/mysql-test/rocksdb/t/i_s_ddl.test | 7 +- .../rocksdb/mysql-test/rocksdb/t/i_s_deadlock.test | 158 + .../mysql-test/rocksdb/t/index_merge_rocksdb.test | 7 +- .../mysql-test/rocksdb/t/index_merge_rocksdb2.test | 4 +- .../mysql-test/rocksdb/t/information_schema.test | 4 +- .../rocksdb/t/insert_optimized_config-master.opt | 1 + storage/rocksdb/mysql-test/rocksdb/t/issue255.test | 32 + .../rocksdb/t/lock_wait_timeout_stats.test | 4 + .../mysql-test/rocksdb/t/mariadb_port_fixes.test | 18 + .../mysql-test/rocksdb/t/max_open_files.test | 53 + .../rocksdb/mysql-test/rocksdb/t/mysqldump.test | 8 +- .../rocksdb/mysql-test/rocksdb/t/mysqldump2.test | 2 +- storage/rocksdb/mysql-test/rocksdb/t/rocksdb.test | 5 +- .../rocksdb/t/rocksdb_checksums-master.opt | 1 + .../mysql-test/rocksdb/t/rocksdb_debug.test | 14 + .../mysql-test/rocksdb/t/rocksdb_range2.test | 1 + .../rocksdb/t/skip_validate_tmp_table.test | 38 +- .../rocksdb/mysql-test/rocksdb/t/transaction.test | 30 + .../rocksdb/t/ttl_primary_read_filtering.test | 19 +- .../rocksdb/mysql-test/rocksdb/t/type_varchar.test | 15 +- .../rocksdb/t/use_direct_reads_writes.test | 64 +- .../rocksdb/mysql-test/rocksdb/t/write_sync.test | 10 +- .../include/rpl_no_unique_check_on_lag.inc | 1 + .../r/singledelete_idempotent_recovery.result | 1 + .../mysql-test/rocksdb_rpl/t/multiclient_2pc.test | 1 + .../t/singledelete_idempotent_recovery.test | 6 + .../rocksdb_stress/r/rocksdb_stress.result | 2 + .../rocksdb_stress/r/rocksdb_stress_crash.result | 2 + .../mysql-test/rocksdb_stress/t/load_generator.py | 13 + .../rocksdb_stress/t/rocksdb_stress.test | 2 + .../rocksdb_stress/t/rocksdb_stress_crash.test | 2 + ...db_allow_to_start_after_corruption_basic.result | 7 + .../r/rocksdb_bytes_per_sync_basic.result | 84 +- .../r/rocksdb_concurrent_prepare_basic.result | 14 - .../rocksdb_flush_memtable_on_analyze_basic.result | 58 - .../r/rocksdb_ignore_unknown_options_basic.result | 14 + .../r/rocksdb_max_open_files_basic.result | 10 +- .../r/rocksdb_max_row_locks_basic.result | 18 +- .../r/rocksdb_two_write_queues_basic.result | 14 + .../r/rocksdb_update_cf_options.result | 38 + .../r/rocksdb_update_cf_options_basic.result | 18 +- .../r/rocksdb_wal_bytes_per_sync_basic.result | 84 +- ...ksdb_allow_to_start_after_corruption_basic.test | 6 + .../t/rocksdb_bytes_per_sync_basic.test | 17 +- .../t/rocksdb_concurrent_prepare_basic.test | 16 - .../t/rocksdb_flush_memtable_on_analyze_basic.test | 46 - .../t/rocksdb_ignore_unknown_options_basic.test | 16 + .../t/rocksdb_max_open_files_basic.test | 10 +- .../t/rocksdb_two_write_queues_basic.test | 16 + .../t/rocksdb_update_cf_options.test | 22 + .../t/rocksdb_update_cf_options_basic.test | 17 +- .../t/rocksdb_wal_bytes_per_sync_basic.test | 18 +- .../mysql-test/storage_engine/type_enum.rdiff | 20 - .../mysql-test/storage_engine/type_set.rdiff | 11 - storage/rocksdb/patch/port/win/io_win.h | 446 -- storage/rocksdb/properties_collector.cc | 145 +- storage/rocksdb/properties_collector.h | 41 +- storage/rocksdb/rdb_buff.h | 2 +- storage/rocksdb/rdb_cf_options.cc | 8 + storage/rocksdb/rdb_cf_options.h | 3 + storage/rocksdb/rdb_compact_filter.h | 2 +- storage/rocksdb/rdb_datadic.cc | 207 +- storage/rocksdb/rdb_datadic.h | 161 +- storage/rocksdb/rdb_i_s.cc | 149 +- storage/rocksdb/rdb_i_s.h | 1 + storage/rocksdb/rdb_io_watchdog.cc | 2 +- storage/rocksdb/rdb_perf_context.cc | 31 +- storage/rocksdb/rdb_perf_context.h | 14 + storage/rocksdb/rdb_psi.cc | 3 +- storage/rocksdb/rdb_psi.h | 3 +- storage/rocksdb/rdb_sst_info.cc | 38 +- storage/rocksdb/rdb_sst_info.h | 6 +- storage/rocksdb/rdb_utils.cc | 33 + storage/rocksdb/rdb_utils.h | 12 +- storage/rocksdb/rocksdb | 2 +- storage/sphinx/ha_sphinx.cc | 12 +- storage/sphinx/snippets_udf.cc | 4 +- storage/spider/spd_conn.cc | 29 +- storage/spider/spd_copy_tables.cc | 84 +- storage/spider/spd_db_conn.cc | 5 +- storage/spider/spd_db_mysql.cc | 21 +- storage/spider/spd_direct_sql.cc | 116 +- storage/spider/spd_i_s.cc | 2 +- storage/spider/spd_include.h | 3 + storage/spider/spd_param.cc | 40 +- storage/spider/spd_param.h | 2 +- storage/spider/spd_ping_table.cc | 22 - storage/spider/spd_sys_table.cc | 37 +- storage/spider/spd_table.cc | 286 +- storage/spider/spd_table.h | 216 +- storage/spider/spd_trx.cc | 19 +- storage/tokudb/CMakeLists.txt | 31 +- storage/tokudb/PerconaFT/.clang-format | 36 + storage/tokudb/PerconaFT/CMakeLists.txt | 6 + storage/tokudb/PerconaFT/README.md | 29 +- storage/tokudb/PerconaFT/ft/ft-ops.cc | 91 +- .../PerconaFT/ft/serialize/block_allocator.cc | 2 +- storage/tokudb/PerconaFT/ft/tests/ft-clock-test.cc | 4 +- storage/tokudb/PerconaFT/ft/tests/log-test4.cc | 2 +- storage/tokudb/PerconaFT/ftcxx/malloc_utils.cpp | 2 +- storage/tokudb/PerconaFT/ftcxx/malloc_utils.hpp | 2 +- storage/tokudb/PerconaFT/portability/memory.cc | 14 +- .../PerconaFT/portability/tests/test-max-data.cc | 2 +- storage/tokudb/PerconaFT/portability/toku_assert.h | 2 +- .../PerconaFT/portability/toku_instrumentation.h | 2 + .../PerconaFT/portability/toku_portability.h | 4 +- .../tokudb/PerconaFT/portability/toku_pthread.h | 6 +- .../tokudb/PerconaFT/portability/toku_race_tools.h | 2 +- storage/tokudb/PerconaFT/portability/toku_time.h | 5 + .../PerconaFT/src/tests/checkpoint_stress.cc | 2 +- .../tokudb/PerconaFT/src/tests/directory_lock.cc | 2 +- .../PerconaFT/src/tests/loader-cleanup-test.cc | 18 +- .../src/tests/recover-del-multiple-abort.cc | 6 +- .../recover-del-multiple-srcdb-fdelete-all.cc | 6 +- .../PerconaFT/src/tests/recover-del-multiple.cc | 6 +- .../src/tests/recover-put-multiple-abort.cc | 6 +- .../PerconaFT/src/tests/recovery_fileops_unit.cc | 4 +- .../tokudb/PerconaFT/src/tests/test-prepare3.cc | 1 + storage/tokudb/PerconaFT/util/scoped_malloc.cc | 2 +- storage/tokudb/ha_tokudb.h | 4 +- storage/tokudb/ha_tokudb_alter_55.cc | 2 +- storage/tokudb/ha_tokudb_alter_56.cc | 16 +- storage/tokudb/ha_tokudb_update.cc | 2 +- storage/tokudb/hatoku_hton.cc | 30 +- .../dir_per_db_rename_to_nonexisting_schema.result | 1 + .../r/partition_debug_sync_tokudb.result | 3 + storage/tokudb/tokudb.cnf | 6 - storage/tokudb/tokudb.cnf.in | 9 + storage/tokudb/tokudb.conf.in | 3 + storage/tokudb/tokudb_sysvars.cc | 12 +- storage/tokudb/tokudb_sysvars.h | 2 +- strings/decimal.c | 4 +- tests/mysql_client_fw.c | 4 +- tests/mysql_client_test.c | 382 +- unittest/sql/mf_iocache-t.cc | 72 +- win/upgrade_wizard/CMakeLists.txt | 3 + 1333 files changed, 51490 insertions(+), 30034 deletions(-) diff --cc mysql-test/main/in_subq_cond_pushdown.result index 56e02c7,0000000..a6246ec mode 100644,000000..100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@@ -1,3801 -1,0 +1,3804 @@@ +CREATE TABLE t1 (a INT, b INT, c INT, d INT); +CREATE TABLE t2 (e INT, f INT, g INT); +CREATE TABLE t3 (x INT, y INT); +INSERT INTO t1 VALUES +(1,1,18,1), (2,1,25,1), (1,3,40,1), (2,3,40,4), +(4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2), +(5,5,65,1), (2,3,70,3), (1,4,35,3), (2,3,25,3), +(2,2,40,4), (1,4,55,1), (5,3,72,4), (1,2,70,5); +INSERT INTO t2 VALUES +(1,2,38), (2,3,15), (1,3,40), (1,4,35), +(2,2,70), (3,4,23), (5,5,12), (5,4,17), +(3,3,17), (4,2,24), (2,5,25), (5,1,65); +INSERT INTO t3 VALUES +(1,25), (1,18), (2,15), (4,24), +(1,35), (3,23), (3,17), (2,15); +CREATE VIEW v1 AS +( +SELECT t3.x AS v1_x, t3.y AS v1_y FROM t3 WHERE t3.x<=3 +); +CREATE VIEW v2 AS +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.e +HAVING max_g>25 +); +# conjunctive subformula : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.c<25 AND +(t1.a,t1.c) IN (SELECT t2.e,MAX(t2.g) FROM t2 WHERE t2.e<5 GROUP BY t2.e); +a b c d +4 2 24 4 +3 2 23 1 +SELECT * FROM t1 +WHERE t1.c<25 AND +(t1.a,t1.c) IN (SELECT t2.e,MAX(t2.g) FROM t2 WHERE t2.e<5 GROUP BY t2.e); +a b c d +4 2 24 4 +3 2 23 1 +EXPLAIN SELECT * FROM t1 +WHERE t1.c<25 AND +(t1.a,t1.c) IN (SELECT t2.e,MAX(t2.g) FROM t2 WHERE t2.e<5 GROUP BY t2.e); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.c<25 AND +(t1.a,t1.c) IN (SELECT t2.e,MAX(t2.g) FROM t2 WHERE t2.e<5 GROUP BY t2.e); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c < 25 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` < 25", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# extracted AND formula : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.c>55 AND t1.b<4 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +2 3 70 3 +SELECT * FROM t1 +WHERE t1.c>55 AND t1.b<4 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE t1.c>55 AND t1.b<4 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.c>55 AND t1.b<4 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c > 55 and t1.b < 4 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` > 55 and t2.f < 4", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# extracted OR formula : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.c>60 OR t1.c<25) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +4 2 24 4 +2 3 70 3 +SELECT * FROM t1 +WHERE (t1.c>60 OR t1.c<25) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +4 2 24 4 +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1.c>60 OR t1.c<25) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.c>60 OR t1.c<25) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` > 60 or `MAX(t2.g)` < 25", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# extracted AND-OR formula : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1.c>60 OR t1.c<25) AND t1.b>2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +2 3 70 3 +SELECT * FROM t1 +WHERE ((t1.c>60 OR t1.c<25) AND t1.b>2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE ((t1.c>60 OR t1.c<25) AND t1.b>2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1.c>60 OR t1.c<25) AND t1.b>2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.b > 2 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "(`MAX(t2.g)` > 60 or `MAX(t2.g)` < 25) and t2.f > 2", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# conjunctive subformula : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.d>3) AND t1.b>1) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +4 2 24 4 +1 2 40 2 +SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.d>3) AND t1.b>1) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +4 2 24 4 +1 2 40 2 +EXPLAIN SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.d>3) AND t1.b>1) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.d>3) AND t1.b>1) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.a < 2 or t1.d > 3) and t1.b > 1 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "t2.f > 1", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# using view IN subquery defINition : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.c>20 AND +(t1.a,t1.c) IN +( +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x +) +; +a b c d +3 2 23 1 +SELECT * FROM t1 +WHERE t1.c>20 AND +(t1.a,t1.c) IN +( +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x +) +; +a b c d +3 2 23 1 +EXPLAIN SELECT * FROM t1 +WHERE t1.c>20 AND +(t1.a,t1.c) IN +( +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.c>20 AND +(t1.a,t1.c) IN +( +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c > 20 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["v1_x", "MAX(v1_y)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(v1_y)` > 20", + "temporary_table": { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x > 1 and t3.x <= 3" + } + } + } + } + } + } +} +# using equality : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1,v1 +WHERE t1.c>20 AND t1.c=v1_y AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d v1_x v1_y +3 2 23 1 3 23 +SELECT * FROM t1,v1 +WHERE t1.c>20 AND t1.c=v1_y AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d v1_x v1_y +3 2 23 1 3 23 +EXPLAIN SELECT * FROM t1,v1 +WHERE t1.c>20 AND t1.c=v1_y AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t3.y 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1,v1 +WHERE t1.c>20 AND t1.c=v1_y AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.y > 20 and t3.x <= 3 and t3.y is not null" + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "t1.c = t3.y and t1.a is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t3.y"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` > 20", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a<2 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 3 40 1 +1 2 40 2 +SELECT * FROM t1 +WHERE t1.a<2 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 3 40 1 +1 2 40 2 +EXPLAIN SELECT * FROM t1 +WHERE t1.a<2 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a<2 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a < 2 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e < 2" + } + } + } + } + } + } +} +# extracted AND formula : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>2 AND t1.a<5 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +4 2 24 4 +3 2 23 1 +SELECT * FROM t1 +WHERE t1.a>2 AND t1.a<5 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +4 2 24 4 +3 2 23 1 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>2 AND t1.a<5 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>2 AND t1.a<5 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 2 and t1.a < 5 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e > 2 and t2.e < 5" + } + } + } + } + } + } +} +# extracted OR formula : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.a<2 OR t1.a>=4) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 3 40 1 +4 2 24 4 +1 2 40 2 +SELECT * FROM t1 +WHERE (t1.a<2 OR t1.a>=4) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 3 40 1 +4 2 24 4 +1 2 40 2 +EXPLAIN SELECT * FROM t1 +WHERE (t1.a<2 OR t1.a>=4) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.a<2 OR t1.a>=4) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.a < 2 or t1.a >= 4) and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e >= 4)" + } + } + } + } + } + } +} +# extracted AND-OR formula : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +1 4 35 3 +SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +1 4 35 3 +EXPLAIN SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" + } + } + } + } + } + } +} +# extracted AND-OR formula : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +1 4 35 3 +SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +1 4 35 3 +EXPLAIN SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1.b<3 OR t1.d>2) AND t1.a<2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 2 40 2 +SELECT * FROM t1 +WHERE ((t1.b<3 OR t1.d>2) AND t1.a<2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 2 40 2 +EXPLAIN SELECT * FROM t1 +WHERE ((t1.b<3 OR t1.d>2) AND t1.a<2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1.b<3 OR t1.d>2) AND t1.a<2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.b < 3 or t1.d > 2) and t1.a < 2 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e < 2" + } + } + } + } + } + } +} +# using equalities : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.d=1 AND t1.a=t1.d AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 3 40 1 +SELECT * FROM t1 +WHERE t1.d=1 AND t1.a=t1.d AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 3 40 1 +EXPLAIN SELECT * FROM t1 +WHERE t1.d=1 AND t1.a=t1.d AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 const,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.d=1 AND t1.a=t1.d AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.d = 1 and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["const", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e = 1" + } + } + } + } + } +} +# using equality : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.d>1 AND t1.a=t1.d AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +4 2 24 4 +SELECT * FROM t1 +WHERE t1.d>1 AND t1.a=t1.d AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +4 2 24 4 +EXPLAIN SELECT * FROM t1 +WHERE t1.d>1 AND t1.a=t1.d AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.d>1 AND t1.a=t1.d AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.d = t1.a and t1.a > 1 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e > 1" + } + } + } + } + } + } +} +# using view IN subquery definition : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a<3 AND +(t1.a,t1.c) IN +( +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x +) +; +a b c d +SELECT * FROM t1 +WHERE t1.a<3 AND +(t1.a,t1.c) IN +( +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x +) +; +a b c d +EXPLAIN SELECT * FROM t1 +WHERE t1.a<3 AND +(t1.a,t1.c) IN +( +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a<3 AND +(t1.a,t1.c) IN +( +SELECT v1_x,MAX(v1_y) +FROM v1 +WHERE v1_x>1 +GROUP BY v1_x +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a < 3 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["v1_x", "MAX(v1_y)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x > 1 and t3.x <= 3 and t3.x < 3" + } + } + } + } + } + } +} +# using equality : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1,v1 +WHERE t1.a=v1_x AND v1_x<2 AND v1_y>30 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d v1_x v1_y +1 3 40 1 1 35 +1 2 40 2 1 35 +SELECT * FROM t1,v1 +WHERE t1.a=v1_x AND v1_x<2 AND v1_y>30 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d v1_x v1_y +1 3 40 1 1 35 +1 2 40 2 1 35 +EXPLAIN SELECT * FROM t1,v1 +WHERE t1.a=v1_x AND v1_x<2 AND v1_y>30 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t3.x,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1,v1 +WHERE t1.a=v1_x AND v1_x<2 AND v1_y>30 AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x < 2 and t3.y > 30 and t3.x <= 3 and t3.x is not null" + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "t1.a = t3.x and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t3.x", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e <= 3" + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE +# extracted OR formula : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE ((t1.b<3 OR t1.b=4) AND t1.a<3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 2 40 2 +SELECT * FROM t1 +WHERE ((t1.b<3 OR t1.b=4) AND t1.a<3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 2 40 2 +EXPLAIN SELECT * FROM t1 +WHERE ((t1.b<3 OR t1.b=4) AND t1.a<3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE ((t1.b<3 OR t1.b=4) AND t1.a<3) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.b < 3 or t1.b = 4) and t1.a < 3 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "t2.f < 3 or t2.f = 4", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e < 3" + } + } + } + } + } + } +} +# conjunctive subformula using addition : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.a+t1.c>41) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +2 3 70 3 +SELECT * FROM t1 +WHERE (t1.a+t1.c>41) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1.a+t1.c>41) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.a+t1.c>41) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a + t1.c > 41 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "t2.e + `MAX(t2.g)` > 41", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# conjunctive subformula using substitution : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.c-t1.a<35) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +4 2 24 4 +3 2 23 1 +SELECT * FROM t1 +WHERE (t1.c-t1.a<35) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +4 2 24 4 +3 2 23 1 +EXPLAIN SELECT * FROM t1 +WHERE (t1.c-t1.a<35) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.c-t1.a<35) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c - t1.a < 35 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` - t2.e < 35", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# conjunctive subformula using multiplication : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.c*t1.a>100) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +2 3 70 3 +SELECT * FROM t1 +WHERE (t1.c*t1.a>100) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1.c*t1.a>100) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.c*t1.a>100) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c * t1.a > 100 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` * t2.e > 100", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# conjunctive subformula using division : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.c/t1.a>30) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 3 40 1 +1 2 40 2 +2 3 70 3 +SELECT * FROM t1 +WHERE (t1.c/t1.a>30) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 3 40 1 +1 2 40 2 +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1.c/t1.a>30) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.c/t1.a>30) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c / t1.a > 30 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` / t2.e > 30", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# conjunctive subformula using BETWEEN : pushing into HAVING +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.c BETWEEN 50 AND 100) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +2 3 70 3 +SELECT * FROM t1 +WHERE (t1.c BETWEEN 50 AND 100) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1.c BETWEEN 50 AND 100) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.c BETWEEN 50 AND 100) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c between 50 and 100 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` between 50 and 100", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + } + } + } + } +} +# conjunctive subformula using addition : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.a+t1.b > 5) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +4 2 24 4 +SELECT * FROM t1 +WHERE (t1.a+t1.b > 5) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +4 2 24 4 +EXPLAIN SELECT * FROM t1 +WHERE (t1.a+t1.b > 5) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.a+t1.b > 5) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a + t1.b > 5 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e + t2.f > 5" + } + } + } + } + } + } +} +# conjunctive subformula using substitution : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.a-t1.b > 0) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +4 2 24 4 +SELECT * FROM t1 +WHERE (t1.a-t1.b > 0) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +4 2 24 4 +EXPLAIN SELECT * FROM t1 +WHERE (t1.a-t1.b > 0) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.a-t1.b > 0) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a - t1.b > 0 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e - t2.f > 0" + } + } + } + } + } + } +} +# conjunctive subformula using multiplication : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.a*t1.b > 6) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +4 2 24 4 +SELECT * FROM t1 +WHERE (t1.a*t1.b > 6) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +4 2 24 4 +EXPLAIN SELECT * FROM t1 +WHERE (t1.a*t1.b > 6) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.a*t1.b > 6) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a * t1.b > 6 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e * t2.f > 6" + } + } + } + } + } + } +} +# conjunctive subformula using division : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.b/t1.a > 2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +1 3 40 1 +1 4 35 3 +SELECT * FROM t1 +WHERE (t1.b/t1.a > 2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +a b c d +1 3 40 1 +1 4 35 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1.b/t1.a > 2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.b/t1.a > 2) AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e,t2.f +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.b / t1.a > 2 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.f / t2.e > 2" + } + } + } + } + } + } +} +# conjunctive subformula using BETWEEN : pushing into WHERE +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.a BETWEEN 1 AND 3) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 3 40 1 +3 2 23 1 +1 2 40 2 +2 3 70 3 +SELECT * FROM t1 +WHERE (t1.a BETWEEN 1 AND 3) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +a b c d +1 3 40 1 +3 2 23 1 +1 2 40 2 +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE (t1.a BETWEEN 1 AND 3) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.a BETWEEN 1 AND 3) AND +(t1.a,t1.c) IN +( +SELECT t2.e,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a between 1 and 3 and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e between 1 and 3" + } + } + } + } + } + } +} +# conjunctive subformula : pushing into HAVING of the IN subquery +# conjunctive subformula : pushing into WHERE of the view from the IN subquery +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.c>3 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +1 2 40 2 +2 3 70 3 +SELECT * FROM t1 +WHERE t1.c>3 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +1 2 40 2 +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE t1.c>3 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary - 3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort ++3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.c>3 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c > 3 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(v2.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "v2.max_g > 3", + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v2.e < 5", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, - "filtered": 100 ++ "filtered": 100, ++ "attached_condition": "t2.e < 5" + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE of the IN subquery +# conjunctive subformula : pushing into WHERE of the view +# from the IN subquery +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +2 3 70 3 +SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary - 3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort ++3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(v2.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v2.e < 5 and v2.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, - "filtered": 100 ++ "filtered": 100, ++ "attached_condition": "t2.e < 5 and t2.e > 1" + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE and HAVING +# of the IN subquery +# conjunctive subformula : pushing into WHERE of the view +# from the IN subquery +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>1 AND t1.c<100 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +2 3 70 3 +SELECT * FROM t1 +WHERE t1.a>1 AND t1.c<100 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>1 AND t1.c<100 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary - 3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort ++3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>1 AND t1.c<100 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 100 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(v2.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "v2.max_g < 100", + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v2.e < 5 and v2.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, - "filtered": 100 ++ "filtered": 100, ++ "attached_condition": "t2.e < 5 and t2.e > 1" + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE of the IN subquery +# extracted AND formula : pushing into HAVING of the derived table +# from the IN subquery +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "d_tab.e < 5 and d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, - "having_condition": "max_g > 25", ++ "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", + "filesort": { + "sort_key": "t2.f", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into HAVING of the derived table +# conjunctive subformula : pushing into WHERE of the IN subquery from +# the derived table +SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.e<5 +GROUP BY t2.e +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.b>2; +x y a b max_c +2 15 2 3 70 +2 15 2 3 70 +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "d_tab.e < 5 and d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, - "having_condition": "max_g > 25", ++ "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", + "filesort": { + "sort_key": "t2.f", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE of the derived table +# extracted AND formula : pushing into WHERE of the IN subquery from +# the derived table +SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5; +x y a b max_c +2 15 2 3 70 +4 24 4 2 24 +2 15 2 3 70 +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "d_tab.e < 5 and d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, - "having_condition": "max_g > 25", ++ "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", + "filesort": { + "sort_key": "t2.f", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE and HAVING +# of the derived table +# extracted AND formula : pushing into WHERE of the IN subquery +# from the derived table +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +x y a b max_c +4 24 4 2 24 +SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +x y a b max_c +4 24 4 2 24 +EXPLAIN SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t3.x 2 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 16 Using where; Using temporary; Using filesort +2 DERIVED <subquery3> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x < 5 and t3.x is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.x"], + "rows": 2, + "filtered": 100, + "attached_condition": "d_tab.max_c < 70", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "max_c < 70", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 5 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "having_condition": "t2.f < 5", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e > 1 and t2.e < 5" + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE of the derived table +# conjunctive subformula : pushing into HAVING of the IN subquery from +# the derived table +SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE (t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.f<4 +GROUP BY t2.f +) +GROUP BY t1.a +HAVING t1.b<5 +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5; +x y a b max_c +1 25 1 2 70 +1 18 1 2 70 +2 15 2 3 40 +1 35 1 2 70 +2 15 2 3 40 +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +x y a b max_c +4 24 4 2 24 +SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +x y a b max_c +4 24 4 2 24 +EXPLAIN SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t3.x 2 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 16 Using where; Using temporary; Using filesort +2 DERIVED <subquery3> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x < 5 and t3.x is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.x"], + "rows": 2, + "filtered": 100, + "attached_condition": "d_tab.max_c < 70", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "max_c < 70", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 5 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "having_condition": "t2.f < 5", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e > 1 and t2.e < 5" + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE +# using WINDOW FUNCTIONS : using MAX function +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, MAX(t2.g) OVER (PARTITION BY t2.f) +FROM t2 +WHERE t2.e<5 +) +; +a b c d +1 3 40 1 +2 3 40 4 +1 4 35 3 +1 2 70 5 +SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, MAX(t2.g) OVER (PARTITION BY t2.f) +FROM t2 +WHERE t2.e<5 +) +; +a b c d +1 3 40 1 +2 3 40 4 +1 4 35 3 +1 2 70 5 +EXPLAIN SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, MAX(t2.g) OVER (PARTITION BY t2.f) +FROM t2 +WHERE t2.e<5 +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.b,test.t1.c 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, MAX(t2.g) OVER (PARTITION BY t2.f) +FROM t2 +WHERE t2.e<5 +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.b > 1 and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["f", "MAX(t2.g) OVER (PARTITION BY t2.f)"], + "ref": ["test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t2.f" + } + }, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.f > 1" + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE +# using WINDOW FUNCTIONS : using SUM function +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT) +FROM t2 +WHERE t2.e<5 +) +; +a b c d +5 3 72 4 +SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT) +FROM t2 +WHERE t2.e<5 +) +; +a b c d +5 3 72 4 +EXPLAIN SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT) +FROM t2 +WHERE t2.e<5 +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.b,test.t1.c 1 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT) +FROM t2 +WHERE t2.e<5 +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.b > 1 and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["f", "CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT)"], + "ref": ["test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "attached_condition": "t1.c = `<subquery2>`.`CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT)`", + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t2.f" + } + }, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.f > 1" + } + } + } + } + } + } + } +} +DROP TABLE t1,t2,t3; +DROP VIEW v1,v2; diff --cc sql/field.h index eb4be46,2079c90..b6f2880 --- a/sql/field.h +++ b/sql/field.h @@@ -1368,22 -1400,9 +1380,21 @@@ public orig_table= table= table_arg; set_table_name(&table_arg->alias); } - + virtual void init_for_tmp_table(Field *org_field, TABLE *new_table) + { + init(new_table); + orig_table= org_field->orig_table; + vcol_info= 0; + cond_selectivity= 1.0; + next_equal_field= NULL; + option_list= NULL; + option_struct= NULL; + if (org_field->type() == MYSQL_TYPE_VAR_STRING || + org_field->type() == MYSQL_TYPE_VARCHAR) + new_table->s->db_create_options|= HA_OPTION_PACK_RECORD; + } /* maximum possible display length */ - virtual uint32 max_display_length()= 0; - + virtual uint32 max_display_length() const= 0; /** Whether a field being created is compatible with a existing one. diff --cc sql/item.h index 117c6b5,dcbe41d..c8ec16a --- a/sql/item.h +++ b/sql/item.h @@@ -1647,9 -1571,10 +1684,10 @@@ public set field of temporary table for Item which can be switched on temporary table during query processing (grouping and so on) */ + virtual void set_result_field(Field *field) {} virtual bool is_result_field() { return 0; } - virtual bool is_bool_type() { return false; } virtual bool is_json_type() { return false; } + virtual bool is_bool_literal() const { return false; } /* This is to handle printing of default values */ virtual bool need_parentheses_in_default() { return false; } virtual void save_in_result_field(bool no_conversions) {} @@@ -1923,21 -1840,13 +1961,19 @@@ return Type_handler::type_handler_long_or_longlong(max_char_length()); } - virtual Field *create_tmp_field(bool group, TABLE *table) - { - return tmp_table_field_from_field_type(table); - } - + /** + Create field for temporary table. + @param table Temporary table + @param [OUT] src Who created the fields + @param param Create parameters + @retval NULL (on error) + @retval a pointer to a newly create Field (on success) + */ + virtual Field *create_tmp_field_ex(TABLE *table, + Tmp_field_src *src, + const Tmp_field_param *param)= 0; virtual Item_field *field_for_view_update() { return 0; } - virtual bool vers_trx_id() const - { return false; } virtual Item *neg_transformer(THD *thd) { return NULL; } virtual Item *update_value_transformer(THD *thd, uchar *select_arg) { return this; } @@@ -2676,21 -2536,7 +2712,21 @@@ public based on result_type(), which is less exact. */ Field *create_field_for_create_select(TABLE *table) - { return tmp_table_field_from_field_type(table); } + { return create_table_field_from_handler(table); } + + bool is_valid_limit_clause_variable_with_error() const + { + /* + In case if the variable has an anchored data type, e.g.: + DECLARE a TYPE OF t1.a; + type_handler() is set to &type_handler_null and this + function detects such variable as not valid in LIMIT. + */ + if (type_handler()->is_limit_clause_valid_type()) + return true; + my_error(ER_WRONG_SPVAR_TYPE_IN_LIMIT, MYF(0)); + return false; + } }; @@@ -3019,15 -2853,9 +3056,15 @@@ public const char *table_name_arg): Item(thd), field(par_field), db_name(db_arg), table_name(table_name_arg) { - Type_std_attributes::set(par_field); + Type_std_attributes::set(par_field->type_std_attributes()); } enum Type type() const { return FIELD_ITEM; } + Field *create_tmp_field_ex(TABLE *table, Tmp_field_src *src, + const Tmp_field_param *param) + { + DBUG_ASSERT(0); + return 0; + } double val_real() { return field->val_real(); } longlong val_int() { return field->val_int(); } String *val_str(String *str) { return field->val_str(str); } @@@ -3143,16 -2971,7 +3180,12 @@@ public return &type_handler_null; return field->type_handler(); } + Field *create_tmp_field_from_item_field(TABLE *new_table, + Item_ref *orig_item, + const Tmp_field_param *param); + Field *create_tmp_field_ex(TABLE *table, Tmp_field_src *src, + const Tmp_field_param *param); TYPELIB *get_typelib() const { return field->get_typelib(); } - uint32 field_flags() const - { - return field->flags; - } enum_monotonicity_info get_monotonicity_info() const { return MONOTONIC_STRICT_INCREASING; diff --cc sql/opt_subselect.cc index a55ce2d,a04a67c..800ee65 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@@ -6316,420 -5972,6 +6327,421 @@@ bool JOIN::choose_tableless_subquery_pl tmp_having= having; } } + exec_const_cond= conds; return FALSE; } + + +/* + Check if the item exists in the fields list of the left part of + the IN subquery predicate subq_pred and returns its corresponding + item from the select of the right part of subq_pred. +*/ +Item *Item::get_corresponding_field_in_insubq(Item_in_subselect *subq_pred) +{ + DBUG_ASSERT(type() == Item::FIELD_ITEM || + (type() == Item::REF_ITEM && + ((Item_ref *) this)->ref_type() == Item_ref::VIEW_REF)); + + List_iterator<Field_pair> it(subq_pred->corresponding_fields); + Field_pair *ret; + Item_field *field_item= (Item_field *) (real_item()); + while ((ret= it++)) + { + if (field_item->field == ret->field) + return ret->corresponding_item; + } + return NULL; +} + + +bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) +{ + if (((Item *)this)->get_corresponding_field_in_insubq(subq_pred)) + return true; + if (item_equal) + { + Item_equal_fields_iterator it(*item_equal); + Item *equal_item; + while ((equal_item= it++)) + { + if (equal_item->const_item()) + continue; + if (equal_item->get_corresponding_field_in_insubq(subq_pred)) + return true; + } + } + return false; +} + + +bool Item_direct_view_ref::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) +{ + if (item_equal) + { + DBUG_ASSERT(real_item()->type() == Item::FIELD_ITEM); + if (((Item *)this)->get_corresponding_field_in_insubq(subq_pred)) + return true; + } + return (*ref)->excl_dep_on_in_subq_left_part(subq_pred); +} + + +bool Item_equal::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) +{ + Item *left_item = get_const(); + Item_equal_fields_iterator it(*this); + Item *item; + if (!left_item) + { + while ((item=it++)) + { + if (item->excl_dep_on_in_subq_left_part(subq_pred)) + { + left_item= item; + break; + } + } + } + if (!left_item) + return false; + while ((item=it++)) + { + if (item->excl_dep_on_in_subq_left_part(subq_pred)) + return true; + } + return false; +} + + +/** + @brief + Get corresponding item from the select of the right part of IN subquery + + @param thd the thread handle + @param item the item from the left part of subq_pred for which + corresponding item should be found + @param subq_pred the IN subquery predicate + + @details + This method looks through the fields of the select of the right part of + the IN subquery predicate subq_pred trying to find the corresponding + item 'new_item' for item. If item has equal items it looks through + the fields of the select of the right part of subq_pred for each equal + item trying to find the corresponding item. + The method assumes that the given item is either a field item or + a reference to a field item. + + @retval <item*> reference to the corresponding item + @retval NULL if item was not found +*/ + +static +Item *get_corresponding_item(THD *thd, Item *item, + Item_in_subselect *subq_pred) +{ + DBUG_ASSERT(item->type() == Item::FIELD_ITEM || + (item->type() == Item::REF_ITEM && + ((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF)); + + Item *corresonding_item; + Item_equal *item_equal= item->get_item_equal(); + + if (item_equal) + { + Item_equal_fields_iterator it(*item_equal); + Item *equal_item; + while ((equal_item= it++)) + { + corresonding_item= + equal_item->get_corresponding_field_in_insubq(subq_pred); + if (corresonding_item) + return corresonding_item; + } + return NULL; + } + else + return item->get_corresponding_field_in_insubq(subq_pred); +} + + +Item *Item_field::in_subq_field_transformer_for_where(THD *thd, uchar *arg) +{ + Item_in_subselect *subq_pred= (Item_in_subselect *)arg; + Item *producing_item= get_corresponding_item(thd, this, subq_pred); + if (producing_item) + return producing_item->build_clone(thd); + return this; +} + + +Item *Item_direct_view_ref::in_subq_field_transformer_for_where(THD *thd, + uchar *arg) +{ + if (item_equal) + { + Item_in_subselect *subq_pred= (Item_in_subselect *)arg; + Item *producing_item= get_corresponding_item(thd, this, subq_pred); + DBUG_ASSERT (producing_item != NULL); + return producing_item->build_clone(thd); + } + return this; +} + + +/** + @brief + Transforms item so it can be pushed into the IN subquery HAVING clause + + @param thd the thread handle + @param in_item the item for which pushable item should be created + @param subq_pred the IN subquery predicate + + @details + This method finds for in_item that is a field from the left part of the + IN subquery predicate subq_pred its corresponding item from the right part + of subq_pred. + If corresponding item is found, a shell for this item is created. + This shell can be pushed into the HAVING part of subq_pred select. + + @retval <item*> reference to the created corresponding item shell for in_item + @retval NULL if mistake occurs +*/ + +static Item* +get_corresponding_item_for_in_subq_having(THD *thd, Item *in_item, + Item_in_subselect *subq_pred) +{ + Item *new_item= get_corresponding_item(thd, in_item, subq_pred); + + if (new_item) + { + Item_ref *ref= + new (thd->mem_root) Item_ref(thd, + &subq_pred->unit->first_select()->context, + NullS, NullS, + &new_item->name); + if (!ref) + DBUG_ASSERT(0); + return ref; + } + return new_item; +} + + +Item *Item_field::in_subq_field_transformer_for_having(THD *thd, uchar *arg) +{ + return get_corresponding_item_for_in_subq_having(thd, this, + (Item_in_subselect *)arg); +} + + +Item *Item_direct_view_ref::in_subq_field_transformer_for_having(THD *thd, + uchar *arg) +{ + if (!item_equal) + return this; + else + { + Item *new_item= get_corresponding_item_for_in_subq_having(thd, this, + (Item_in_subselect *)arg); + if (!new_item) + return this; + return new_item; + } +} + + +/** + @brief + Find fields that are used in the GROUP BY of the select + + @param thd the thread handle + @param sel the select of the IN subquery predicate + @param fields fields of the left part of the IN subquery predicate + @param grouping_list GROUP BY clause + + @details + This method traverses fields which are used in the GROUP BY of + sel and saves them with their corresponding items from fields. +*/ + +bool grouping_fields_in_the_in_subq_left_part(THD *thd, + st_select_lex *sel, + List<Field_pair> *fields, + ORDER *grouping_list) +{ + DBUG_ENTER("grouping_fields_in_the_in_subq_left_part"); + sel->grouping_tmp_fields.empty(); + List_iterator<Field_pair> it(*fields); + Field_pair *item; + while ((item= it++)) + { + for (ORDER *ord= grouping_list; ord; ord= ord->next) + { + if ((*ord->item)->eq(item->corresponding_item, 0)) + { + if (sel->grouping_tmp_fields.push_back(item, thd->mem_root)) + DBUG_RETURN(TRUE); + } + } + } + DBUG_RETURN(FALSE); +} + + +/** + @brief + Extract condition that can be pushed into select of this IN subquery + + @param thd the thread handle + @param cond current condition + + @details + This function builds the most restrictive condition depending only on + the list of fields of the left part of this IN subquery predicate + (directly or indirectly through equality) that can be extracted from the + given condition cond and pushes it into this IN subquery. + + Example of the transformation: + + SELECT * FROM t1 + WHERE a>3 AND b>10 AND + (a,b) IN (SELECT x,MAX(y) FROM t2 GROUP BY x); + + => + + SELECT * FROM t1 + WHERE a>3 AND b>10 AND + (a,b) IN (SELECT x,max(y) + FROM t2 + WHERE x>3 + GROUP BY x + HAVING MAX(y)>10); + + + In details: + 1. Check what pushable formula can be extracted from cond + 2. Build a clone PC of the formula that can be extracted + (the clone is built only if the extracted formula is a AND subformula + of cond or conjunction of such subformulas) + 3. If there is no HAVING clause prepare PC to be conjuncted with + WHERE clause of this subquery. Otherwise do 4-7. + 4. Check what formula PC_where can be extracted from PC to be pushed + into the WHERE clause of the subquery + 5. Build PC_where and if PC_where is a conjunct(s) of PC remove it from PC + getting PC_having + 6. Prepare PC_where to be conjuncted with the WHERE clause of + the IN subquery + 7. Prepare PC_having to be conjuncted with the HAVING clause of + the IN subquery + + @note + This method is similar to pushdown_cond_for_derived() + + @retval TRUE if an error occurs + @retval FALSE otherwise +*/ + +bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) +{ + DBUG_ENTER("Item_in_subselect::pushdown_cond_for_in_subquery"); + Item *remaining_cond= NULL; + + if (!cond) + DBUG_RETURN(FALSE); + + st_select_lex *sel = unit->first_select(); + + if (is_jtbm_const_tab) + DBUG_RETURN(FALSE); + + if (!sel->cond_pushdown_is_allowed()) + DBUG_RETURN(FALSE); + + /* + Create a list of Field_pair items for this IN subquery. + It consists of the pairs of fields from the left part of this IN subquery + predicate 'left_part' and the respective fields from the select of the + right part of the IN subquery 'sel' (the field from left_part with the + corresponding field from the sel projection list). + Attach this list to the IN subquery. + */ + corresponding_fields.empty(); + List_iterator_fast<Item> it(sel->join->fields_list); + Item *item; + for (uint i= 0; i < left_expr->cols(); i++) + { + item= it++; + Item *elem= left_expr->element_index(i); + + if (elem->real_item()->type() != Item::FIELD_ITEM) + continue; + + if (corresponding_fields.push_back( + new Field_pair(((Item_field *)(elem->real_item()))->field, + item))) + DBUG_RETURN(TRUE); + } + + /* 1. Check what pushable formula can be extracted from cond */ + Item *extracted_cond; + cond->check_pushable_cond(&Item::pushable_cond_checker_for_subquery, + (uchar *)this); + /* 2. Build a clone PC of the formula that can be extracted */ + extracted_cond= + cond->build_pushable_cond(thd, + &Item::pushable_equality_checker_for_subquery, + (uchar *)this); + /* Nothing to push */ + if (!extracted_cond) + { + DBUG_RETURN(FALSE); + } + + /* Collect fields that are used in the GROUP BY of sel */ + st_select_lex *save_curr_select= thd->lex->current_select; + if (sel->have_window_funcs()) + { + if (sel->group_list.first || sel->join->implicit_grouping) + goto exit; + ORDER *common_partition_fields= + sel->find_common_window_func_partition_fields(thd); + if (!common_partition_fields) + goto exit; + + if (grouping_fields_in_the_in_subq_left_part(thd, sel, &corresponding_fields, + common_partition_fields)) + DBUG_RETURN(TRUE); + } + else if (grouping_fields_in_the_in_subq_left_part(thd, sel, + &corresponding_fields, + sel->group_list.first)) + DBUG_RETURN(TRUE); + + /* Do 4-6 */ + sel->pushdown_cond_into_where_clause(thd, extracted_cond, + &remaining_cond, + &Item::in_subq_field_transformer_for_where, + (uchar *) this); + if (!remaining_cond) + goto exit; + /* + 7. Prepare PC_having to be conjuncted with the HAVING clause of + the IN subquery + */ + remaining_cond= + remaining_cond->transform(thd, + &Item::in_subq_field_transformer_for_having, + (uchar *)this); + if (!remaining_cond) + goto exit; + + remaining_cond->walk(&Item::cleanup_excluding_const_fields_processor, + 0, 0); + sel->cond_pushed_into_having= remaining_cond; + +exit: + thd->lex->current_select= save_curr_select; + DBUG_RETURN(FALSE); +} diff --cc sql/sql_lex.cc index 93810d2,fc1af98..779b6d9 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@@ -6872,10 -7003,14 +7000,11 @@@ Item *LEX::create_item_limit(THD *thd // Qualified %TYPE variables are not possible DBUG_ASSERT(!spv->field_def.column_type_ref()); Item_splocal *item; - if (!(item= create_item_spvar_row_field(thd, rh, a, b, spv, start, end))) + if (unlikely(!(item= create_item_spvar_row_field(thd, rh, &sa, &sb, spv, + ca->pos(), cb->end())))) return NULL; - if (unlikely(item->type() != Item::INT_ITEM)) - { - my_error(ER_WRONG_SPVAR_TYPE_IN_LIMIT, MYF(0)); + if (!item->is_valid_limit_clause_variable_with_error()) return NULL; - } item->limit_clause_param= true; return item; } @@@ -7752,126 -7902,275 +7895,400 @@@ Item *Lex_trim_st::make_item_func_trim( } +/** + @brief + Extract from given item a condition pushable into WHERE clause + + @param thd the thread handle + @param cond the item to extract a condition to be pushed + into WHERE + @param remaining_cond the condition that will remain of cond after + the pushdown of its parts into the WHERE clause + @param transformer the transformer callback function to be + applied to the condition so it can be pushed + down into the WHERE clause of this select + @param arg parameter to be passed to the transformer + + @details + This method checks if cond entirely or its parts can be + pushed into the WHERE clause of this select and prepares it for pushing. + + First it checks wherever this select doesn't have any aggregation function + in its projection and GROUP BY clause. If so cond can be entirely + pushed into the WHERE clause of this select but before its fields should + be transformed with transformer_for_where to make it pushable. + + Otherwise the method checks wherever any condition depending only on + grouping fields can be extracted from cond. If there is any it prepares it + for pushing using grouping_field_transformer_for_where and if it happens to + be a conjunct of cond it removes it from cond. It saves the result of + removal in remaining_cond. + The extracted condition is saved in cond_pushed_into_where of this select. + + @note + When looking for pushable condition the method considers only the grouping + fields from the list grouping_tmp_fields whose elements are of the type + Field_pair. This list must be prepared before the call of the + function. + + @note + This method is called for pushdown conditions into materialized + derived tables/views optimization. + Item::derived_field_transformer_for_where is passed as the actual + callback function. + Also it is called for pushdown conditions into materialized IN subqueries. + Item::in_subq_field_transformer_for_where is passed as the actual + callback function. +*/ + +void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, + Item **remaining_cond, + Item_transformer transformer, + uchar *arg) +{ + if (!cond_pushdown_is_allowed()) + return; + thd->lex->current_select= this; + if (have_window_funcs()) + { + Item *cond_over_partition_fields; + check_cond_extraction_for_grouping_fields(cond); + cond_over_partition_fields= + build_cond_for_grouping_fields(thd, cond, true); + if (cond_over_partition_fields) + cond_over_partition_fields= cond_over_partition_fields->transform(thd, + &Item::grouping_field_transformer_for_where, + (uchar*) this); + if (cond_over_partition_fields) + { + cond_over_partition_fields->walk( + &Item::cleanup_excluding_const_fields_processor, 0, 0); + cond_pushed_into_where= cond_over_partition_fields; + } + + return; + } + + if (!join->group_list && !with_sum_func) + { + cond= + cond->transform(thd, transformer, arg); + if (cond) + { + cond->walk( + &Item::cleanup_excluding_const_fields_processor, 0, 0); + cond_pushed_into_where= cond; + } + + return; + } + + /* + Figure out what can be extracted from cond + that could be pushed into the WHERE clause of this select + */ + Item *cond_over_grouping_fields; + check_cond_extraction_for_grouping_fields(cond); + cond_over_grouping_fields= + build_cond_for_grouping_fields(thd, cond, true); + + /* + Transform the references to the columns from the cond + pushed into the WHERE clause of this select to make them usable in + the new context + */ + if (cond_over_grouping_fields) + cond_over_grouping_fields= cond_over_grouping_fields->transform(thd, + &Item::grouping_field_transformer_for_where, + (uchar*) this); + + if (cond_over_grouping_fields) + { + + /* + In cond remove top conjuncts that has been pushed into the WHERE + clause of this select + */ + cond= remove_pushed_top_conjuncts(thd, cond); + + cond_over_grouping_fields->walk( + &Item::cleanup_excluding_const_fields_processor, 0, 0); + cond_pushed_into_where= cond_over_grouping_fields; + } + + *remaining_cond= cond; +} ++ ++ ++Item *LEX::create_item_qualified_asterisk(THD *thd, ++ const Lex_ident_sys_st *name) ++{ ++ Item *item; ++ if (!(item= new (thd->mem_root) Item_field(thd, current_context(), ++ NullS, name->str, ++ &star_clex_str))) ++ return NULL; ++ current_select->with_wild++; ++ return item; ++} ++ ++ + Item *LEX::make_item_func_call_generic(THD *thd, Lex_ident_cli_st *cdb, + Lex_ident_cli_st *cname, List<Item> *args) + { + Lex_ident_sys db(thd, cdb), name(thd, cname); + if (db.is_null() || name.is_null()) + return NULL; // EOM + /* + The following in practice calls: + <code>Create_sp_func::create()</code> + and builds a stored function. + + However, it's important to maintain the interface between the + parser and the implementation in item_create.cc clean, + since this will change with WL#2128 (SQL PATH): + - INFORMATION_SCHEMA.version() is the SQL 99 syntax for the native + function version(), + - MySQL.version() is the SQL 2003 syntax for the native function + version() (a vendor can specify any schema). + */ + + if (!name.str || check_db_name((LEX_STRING*) static_cast<LEX_CSTRING*>(&db))) + { + my_error(ER_WRONG_DB_NAME, MYF(0), db.str); + return NULL; + } + if (check_routine_name(&name)) + return NULL; + + Create_qfunc *builder= find_qualified_function_builder(thd); + DBUG_ASSERT(builder); + return builder->create_with_db(thd, &db, &name, true, args); + } + + + Item *LEX::create_item_qualified_asterisk(THD *thd, - const Lex_ident_sys_st *name) -{ - Item *item; - if (!(item= new (thd->mem_root) Item_field(thd, current_context(), - NullS, name->str, - &star_clex_str))) - return NULL; - current_select->with_wild++; - return item; -} - - -Item *LEX::create_item_qualified_asterisk(THD *thd, + const Lex_ident_sys_st *a, + const Lex_ident_sys_st *b) + { + Item *item; + const char* schema= thd->client_capabilities & CLIENT_NO_SCHEMA ? + NullS : a->str; + if (!(item= new (thd->mem_root) Item_field(thd, current_context(), + schema, b->str, + &star_clex_str))) + return NULL; + current_select->with_wild++; + return item; + } + + + bool Lex_ident_sys_st::copy_ident_cli(THD *thd, const Lex_ident_cli_st *str) + { + return thd->to_ident_sys_alloc(this, str); + } + + bool Lex_ident_sys_st::copy_keyword(THD *thd, const Lex_ident_cli_st *str) + { + return thd->make_lex_string(static_cast<LEX_CSTRING*>(this), + str->str, str->length) == NULL; + } + + bool Lex_ident_sys_st::copy_or_convert(THD *thd, + const Lex_ident_cli_st *src, + CHARSET_INFO *cs) + { + if (!src->is_8bit()) + return copy_keyword(thd, src); // 7bit string makes a wellformed identifier + return convert(thd, src, cs); + } + + + bool Lex_ident_sys_st::copy_sys(THD *thd, const LEX_CSTRING *src) + { + if (thd->check_string_for_wellformedness(src->str, src->length, + system_charset_info)) + return true; + return thd->make_lex_string(this, src->str, src->length) == NULL; + } + + + bool Lex_ident_sys_st::convert(THD *thd, + const LEX_CSTRING *src, CHARSET_INFO *cs) + { + LEX_STRING tmp; + if (thd->convert_with_error(system_charset_info, &tmp, cs, + src->str, src->length)) + return true; + str= tmp.str; + length= tmp.length; + return false; + } + + + bool Lex_ident_sys_st::to_size_number(ulonglong *to) const + { + ulonglong number; + uint text_shift_number= 0; + longlong prefix_number; + const char *start_ptr= str; + size_t str_len= length; + const char *end_ptr= start_ptr + str_len; + int error; + prefix_number= my_strtoll10(start_ptr, (char**) &end_ptr, &error); + if (likely((start_ptr + str_len - 1) == end_ptr)) + { + switch (end_ptr[0]) + { + case 'g': + case 'G': text_shift_number+=30; break; + case 'm': + case 'M': text_shift_number+=20; break; + case 'k': + case 'K': text_shift_number+=10; break; + default: + my_error(ER_WRONG_SIZE_NUMBER, MYF(0)); + return true; + } + if (unlikely(prefix_number >> 31)) + { + my_error(ER_SIZE_OVERFLOW_ERROR, MYF(0)); + return true; + } + number= prefix_number << text_shift_number; + } + else + { + my_error(ER_WRONG_SIZE_NUMBER, MYF(0)); + return true; + } + *to= number; + return false; + } + + + bool LEX::part_values_current(THD *thd) + { + partition_element *elem= part_info->curr_part_elem; + if (!is_partition_management()) + { + if (unlikely(part_info->part_type != VERSIONING_PARTITION)) + { + my_error(ER_PARTITION_WRONG_TYPE, MYF(0), "SYSTEM_TIME"); + return true; + } + } + else + { + DBUG_ASSERT(create_last_non_select_table); + DBUG_ASSERT(create_last_non_select_table->table_name.str); + // FIXME: other ALTER commands? + my_error(ER_VERS_WRONG_PARTS, MYF(0), + create_last_non_select_table->table_name.str); + return true; + } + elem->type(partition_element::CURRENT); + DBUG_ASSERT(part_info->vers_info); + part_info->vers_info->now_part= elem; + if (unlikely(part_info->init_column_part(thd))) + return true; + return false; + } + + + bool LEX::part_values_history(THD *thd) + { + partition_element *elem= part_info->curr_part_elem; + if (!is_partition_management()) + { + if (unlikely(part_info->part_type != VERSIONING_PARTITION)) + { + my_error(ER_PARTITION_WRONG_TYPE, MYF(0), "SYSTEM_TIME"); + return true; + } + } + else + { + part_info->vers_init_info(thd); + elem->id= UINT_MAX32; + } + DBUG_ASSERT(part_info->vers_info); + if (unlikely(part_info->vers_info->now_part)) + { + DBUG_ASSERT(create_last_non_select_table); + DBUG_ASSERT(create_last_non_select_table->table_name.str); + my_error(ER_VERS_WRONG_PARTS, MYF(0), + create_last_non_select_table->table_name.str); + return true; + } + elem->type(partition_element::HISTORY); + if (unlikely(part_info->init_column_part(thd))) + return true; + return false; + } + + + bool LEX::last_field_generated_always_as_row_start_or_end(Lex_ident *p, + const char *type, + uint flag) + { + if (unlikely(p->str)) + { + my_error(ER_VERS_DUPLICATE_ROW_START_END, MYF(0), type, + last_field->field_name.str); + return true; + } + last_field->flags|= (flag | NOT_NULL_FLAG); + DBUG_ASSERT(p); + *p= last_field->field_name; + return false; + } + + + + bool LEX::last_field_generated_always_as_row_start() + { + Vers_parse_info &info= vers_get_info(); + Lex_ident *p= &info.as_row.start; + return last_field_generated_always_as_row_start_or_end(p, "START", + VERS_SYS_START_FLAG); + } + + + bool LEX::last_field_generated_always_as_row_end() + { + Vers_parse_info &info= vers_get_info(); + Lex_ident *p= &info.as_row.end; + return last_field_generated_always_as_row_start_or_end(p, "END", + VERS_SYS_END_FLAG); + } + + + bool LEX::tvc_finalize() + { + mysql_init_select(this); + if (unlikely(!(current_select->tvc= + new (thd->mem_root) + table_value_constr(many_values, + current_select, + current_select->options)))) + return true; + many_values.empty(); + return false; + } + + + bool LEX::tvc_finalize_derived() + { + derived_tables|= DERIVED_SUBQUERY; + if (unlikely(!expr_allows_subselect || sql_command == (int)SQLCOM_PURGE)) + { + thd->parse_error(); + return true; + } + if (current_select->linkage == GLOBAL_OPTIONS_TYPE || + unlikely(mysql_new_select(this, 1, NULL))) + return true; + current_select->linkage= DERIVED_TABLE_TYPE; + return tvc_finalize(); + } diff --cc sql/sql_lex.h index 89e3abe,e5ca2d4..c3ef33e --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@@ -1239,10 -1340,11 +1341,10 @@@ public With_element *find_table_def_in_with_clauses(TABLE_LIST *table); bool check_unrestricted_recursive(bool only_standard_compliant); bool check_subqueries_with_recursive_references(); - void collect_grouping_fields(THD *thd, ORDER *grouping_list); - void check_cond_extraction_for_grouping_fields(Item *cond, - TABLE_LIST *derived); + void collect_grouping_fields(THD *thd, ORDER *grouping_list); + void check_cond_extraction_for_grouping_fields(Item *cond); Item *build_cond_for_grouping_fields(THD *thd, Item *cond, - bool no_to_clones); + bool no_to_clones); List<Window_spec> window_specs; void prepare_add_window_spec(THD *thd); diff --cc sql/sql_select.cc index 958ab02,3dfc41a..fdd8907 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@@ -1616,29 -1639,13 +1639,32 @@@ JOIN::optimize_inner( if (arena) thd->restore_active_arena(arena, &backup); } - + + if (optimize_constant_subqueries()) + DBUG_RETURN(1); + - if (setup_jtbm_semi_joins(this, join_list, &conds)) + List<Item> eq_list; + + if (setup_degenerate_jtbm_semi_joins(this, join_list, eq_list)) DBUG_RETURN(1); + if (eq_list.elements != 0) + { + Item *new_cond; + + if (eq_list.elements == 1) + new_cond= eq_list.pop(); + else + new_cond= new (thd->mem_root) Item_cond_and(thd, eq_list); + + if (new_cond && + ((new_cond->fix_fields(thd, &new_cond) || + !(conds= and_items(thd, conds, new_cond)) || + conds->fix_fields(thd, &conds)))) + DBUG_RETURN(TRUE); + } + eq_list.empty(); + if (select_lex->cond_pushed_into_where) { conds= and_conds(thd, conds, select_lex->cond_pushed_into_where); diff --cc sql/sql_type.cc index 0c9dc57,22eebaf..07a5fea --- a/sql/sql_type.cc +++ b/sql/sql_type.cc @@@ -5803,486 -5787,61 +5814,543 @@@ void Type_handler_geometry::Item_param_ /***************************************************************************/ +Field *Type_handler_row:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + DBUG_ASSERT(attr->length == 0); + DBUG_ASSERT(f_maybe_null(attr->pack_flag)); + return new (mem_root) Field_row(rec.ptr(), name); +} + + +Field *Type_handler_olddecimal:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_decimal(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + f_decimals(attr->pack_flag), + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag) == 0); +} + + +Field *Type_handler_newdecimal:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_new_decimal(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + f_decimals(attr->pack_flag), + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag) == 0); +} + + +Field *Type_handler_float:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + int decimals= f_decimals(attr->pack_flag); + if (decimals == FLOATING_POINT_DECIMALS) + decimals= NOT_FIXED_DEC; + return new (mem_root) + Field_float(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, decimals, + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag)== 0); +} + + +Field *Type_handler_double:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + int decimals= f_decimals(attr->pack_flag); + if (decimals == FLOATING_POINT_DECIMALS) + decimals= NOT_FIXED_DEC; + return new (mem_root) + Field_double(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, decimals, + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag)== 0); +} + + +Field *Type_handler_tiny:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_tiny(rec.ptr(), (uint32) attr->length, rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag) == 0); +} + + +Field *Type_handler_short:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_short(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag) == 0); +} + + +Field *Type_handler_int24:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_medium(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag) == 0); +} + + +Field *Type_handler_long:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_long(rec.ptr(), (uint32) attr->length, rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag) == 0); +} + + +Field *Type_handler_longlong:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + if (flags & (VERS_SYS_START_FLAG|VERS_SYS_END_FLAG)) + return new (mem_root) + Field_vers_trx_id(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag) == 0); + return new (mem_root) + Field_longlong(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag) == 0); +} + + +Field *Type_handler_timestamp:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new_Field_timestamp(mem_root, + rec.ptr(), rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, share, + attr->temporal_dec(MAX_DATETIME_WIDTH)); +} + + +Field *Type_handler_timestamp2:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_timestampf(rec.ptr(), rec.null_ptr(), rec.null_bit(), + attr->unireg_check, + name, share, attr->temporal_dec(MAX_DATETIME_WIDTH)); +} + + +Field *Type_handler_year:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_year(rec.ptr(), (uint32) attr->length, rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name); +} + + +Field *Type_handler_date:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_date(rec.ptr(),rec.null_ptr(),rec.null_bit(), + attr->unireg_check, name); +} + + +Field *Type_handler_newdate:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_newdate(rec.ptr(), rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name); +} + + +Field *Type_handler_time:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new_Field_time(mem_root, rec.ptr(), rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + attr->temporal_dec(MIN_TIME_WIDTH)); +} + + +Field *Type_handler_time2:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_timef(rec.ptr(), rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + attr->temporal_dec(MIN_TIME_WIDTH)); +} + + +Field *Type_handler_datetime:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new_Field_datetime(mem_root, rec.ptr(), rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + attr->temporal_dec(MAX_DATETIME_WIDTH)); +} + + +Field *Type_handler_datetime2:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_datetimef(rec.ptr(), rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, + attr->temporal_dec(MAX_DATETIME_WIDTH)); +} + + +Field *Type_handler_null:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_null(rec.ptr(), (uint32) attr->length, attr->unireg_check, + name, attr->charset); +} + + +Field *Type_handler_bit:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return f_bit_as_char(attr->pack_flag) ? + new (mem_root) Field_bit_as_char(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name) : + new (mem_root) Field_bit(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + bit.ptr(), bit.offs(), attr->unireg_check, name); +} + + +#ifdef HAVE_SPATIAL +Field *Type_handler_geometry:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + status_var_increment(current_thd->status_var.feature_gis); + return new (mem_root) + Field_geom(rec.ptr(), rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, share, + attr->pack_flag_to_pack_length(), attr->geom_type, attr->srid); +} +#endif + + +Field *Type_handler_string:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_string(rec.ptr(), (uint32) attr->length, + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, attr->charset); +} + + +Field *Type_handler_varchar:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + if (attr->unireg_check == Field::TMYSQL_COMPRESSED) + return new (mem_root) + Field_varstring_compressed(rec.ptr(), (uint32) attr->length, + HA_VARCHAR_PACKLENGTH((uint32) attr->length), + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, share, attr->charset, + zlib_compression_method); + return new (mem_root) + Field_varstring(rec.ptr(), (uint32) attr->length, + HA_VARCHAR_PACKLENGTH((uint32) attr->length), + rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, share, attr->charset); +} + + +Field *Type_handler_blob_common:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + if (attr->unireg_check == Field::TMYSQL_COMPRESSED) + return new (mem_root) + Field_blob_compressed(rec.ptr(), rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, share, + attr->pack_flag_to_pack_length(), attr->charset, + zlib_compression_method); + return new (mem_root) + Field_blob(rec.ptr(), rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, share, + attr->pack_flag_to_pack_length(), attr->charset); +} + + +Field *Type_handler_enum:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_enum(rec.ptr(), (uint32) attr->length, rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, attr->pack_flag_to_pack_length(), + attr->interval, attr->charset); +} + + +Field *Type_handler_set:: + make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const +{ + return new (mem_root) + Field_set(rec.ptr(), (uint32) attr->length, rec.null_ptr(), rec.null_bit(), + attr->unireg_check, name, attr->pack_flag_to_pack_length(), + attr->interval, attr->charset); +} + + +/***************************************************************************/ + +void Type_handler:: + Column_definition_attributes_frm_pack(const Column_definition_attributes *def, + uchar *buff) const +{ + def->frm_pack_basic(buff); + def->frm_pack_charset(buff); +} + + +#ifdef HAVE_SPATIAL +void Type_handler_geometry:: + Column_definition_attributes_frm_pack(const Column_definition_attributes *def, + uchar *buff) const +{ + def->frm_pack_basic(buff); + buff[11]= 0; + buff[14]= (uchar) def->geom_type; +} +#endif + + +/***************************************************************************/ + +bool Type_handler:: + Column_definition_attributes_frm_unpack(Column_definition_attributes *attr, + TABLE_SHARE *share, + const uchar *buffer, + LEX_CUSTRING *gis_options) + const +{ + attr->frm_unpack_basic(buffer); + return attr->frm_unpack_charset(share, buffer); +} + + +#ifdef HAVE_SPATIAL +bool Type_handler_geometry:: + Column_definition_attributes_frm_unpack(Column_definition_attributes *attr, + TABLE_SHARE *share, + const uchar *buffer, + LEX_CUSTRING *gis_options) + const +{ + uint gis_opt_read, gis_length, gis_decimals; + Field_geom::storage_type st_type; + attr->frm_unpack_basic(buffer); + // charset and geometry_type share the same byte in frm + attr->geom_type= (Field::geometry_type) buffer[14]; + gis_opt_read= gis_field_options_read(gis_options->str, + gis_options->length, + &st_type, &gis_length, + &gis_decimals, &attr->srid); + gis_options->str+= gis_opt_read; + gis_options->length-= gis_opt_read; + return false; +} +#endif + + bool Type_handler::Vers_history_point_resolve_unit(THD *thd, + Vers_history_point *point) + const + { + /* + Disallow using non-relevant data types in history points. + Even expressions with explicit TRANSACTION or TIMESTAMP units. + */ + point->bad_expression_data_type_error(name().ptr()); + return true; + } + + + bool Type_handler_typelib:: + Vers_history_point_resolve_unit(THD *thd, + Vers_history_point *point) const + { + /* + ENUM/SET have dual type properties (string and numeric). + Require explicit CAST to avoid ambiguity. + */ + point->bad_expression_data_type_error(name().ptr()); + return true; + } + + + bool Type_handler_general_purpose_int:: + Vers_history_point_resolve_unit(THD *thd, + Vers_history_point *point) const + { + return point->resolve_unit_trx_id(thd); + } + + + bool Type_handler_bit:: + Vers_history_point_resolve_unit(THD *thd, + Vers_history_point *point) const + { + return point->resolve_unit_trx_id(thd); + } + + + bool Type_handler_temporal_result:: + Vers_history_point_resolve_unit(THD *thd, + Vers_history_point *point) const + { + return point->resolve_unit_timestamp(thd); + } + + + bool Type_handler_general_purpose_string:: + Vers_history_point_resolve_unit(THD *thd, + Vers_history_point *point) const + { + return point->resolve_unit_timestamp(thd); + } + /***************************************************************************/ diff --cc sql/sql_type.h index b9d739b,1ddcef2..ad554a9 --- a/sql/sql_type.h +++ b/sql/sql_type.h @@@ -2502,13 -2577,7 +2726,14 @@@ public const Record_addr &addr, const Type_all_attributes &attr, TABLE *table) const; + Field *make_table_field_from_def(TABLE_SHARE *share, + MEM_ROOT *mem_root, + const LEX_CSTRING *name, + const Record_addr &addr, + const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const; + bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p) const; }; diff --cc sql/sql_update.cc index 38638d3,c0fa2b4..13019cd --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@@ -2600,9 -2636,10 +2636,10 @@@ int multi_update::do_updates( uint field_num= 0; do { - if ((local_error= - tbl->file->ha_rnd_pos(tbl->record[0], - (uchar *) tmp_table->field[field_num]->ptr))) ++ uchar *ref= ++ ((Field_varstring *) tmp_table->field[field_num])->get_data(); + if (unlikely((local_error= - tbl->file->ha_rnd_pos(tbl->record[0], - (uchar *) tmp_table-> - field[field_num]->ptr)))) ++ tbl->file->ha_rnd_pos(tbl->record[0], ref)))) { err_table= tbl; goto err; diff --cc storage/innobase/include/srv0srv.h index bd1452a,7c22015..a5a7326 --- a/storage/innobase/include/srv0srv.h +++ b/storage/innobase/include/srv0srv.h @@@ -974,9 -971,10 +968,9 @@@ struct export_var_t ulint innodb_os_log_fsyncs; /*!< fil_n_log_flushes */ ulint innodb_os_log_pending_writes; /*!< srv_os_log_pending_writes */ ulint innodb_os_log_pending_fsyncs; /*!< fil_n_pending_log_flushes */ - ulint innodb_page_size; /*!< UNIV_PAGE_SIZE */ + ulint innodb_page_size; /*!< srv_page_size */ ulint innodb_pages_created; /*!< buf_pool->stat.n_pages_created */ ulint innodb_pages_read; /*!< buf_pool->stat.n_pages_read*/ - ulint innodb_page0_read; /*!< srv_stats.page0_read */ ulint innodb_pages_written; /*!< buf_pool->stat.n_pages_written */ ulint innodb_row_lock_waits; /*!< srv_n_lock_wait_count */ ulint innodb_row_lock_current_waits; /*!< srv_n_lock_wait_current_count */