- commits - lists.mariadb.org

[Commits] 3b234104ae2: MDEV-16955: rocksdb_sys_vars.rocksdb_update_cf_options_basic
by Sergei Petrunia 09 Aug '19

09 Aug '19

revision-id: 3b234104ae227556f06c2c3d227e5fc51692c8fa (mariadb-10.2.26-13-g3b234104ae2) parent(s): 6765cc607764c920bc5fbf4128bb526b73f15c2e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-08-10 01:46:50 +0300 message: MDEV-16955: rocksdb_sys_vars.rocksdb_update_cf_options_basic ... produces "bytes lost" warnings When rocksdb_validate_update_cf_options() returns an error, the update won't happen. Free the copy of the string in this case. --- storage/rocksdb/ha_rocksdb.cc | 2 ++ 1 file changed, 2 insertions(+) diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 4f256b10abc..613ac0884a7 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -14314,6 +14314,8 @@ static int rocksdb_validate_update_cf_options( // then there's no point to proceed. if (!Rdb_cf_options::parse_cf_options(str, &option_map)) { my_error(ER_WRONG_VALUE_FOR_VAR, MYF(0), "rocksdb_update_cf_options", str); + // Free what we've copied with my_strdup above. + my_free((void*)(*(const char **)save)); return HA_EXIT_FAILURE; } // Loop through option_map and create missing column families

1 0

[Commits] 3a8d8b5e3b2: MENT-325: DROP TABLE IF EXISTS killed on master but was replicated
by sujatha 09 Aug '19

09 Aug '19

revision-id: 3a8d8b5e3b2ec0c4f23349c3ab1864fc866f1361 (mariadb-10.2.26-13-g3a8d8b5e3b2) parent(s): 6765cc607764c920bc5fbf4128bb526b73f15c2e author: Sujatha committer: Sujatha timestamp: 2019-08-09 15:06:55 +0530 message: MENT-325: DROP TABLE IF EXISTS killed on master but was replicated Problem: ======= DROP TABLE IF EXISTS was killed. The table still exists on the master but the DDL was still logged. Analysis and Fix: ================ Implemented upstream fix. commit id: 0b5b1aed3a1ec0fcf833cf9ee69dcff04ed874ae We do binlog failed queries when the query can generate 'partial results'. When a list of tables are dropped as part of a single 'DROP TABLE' command and if it fails, it can generate partial results and in such cases we log it with an expected error into the binary log so that slave's state is also the same as that of the master. But when the DROP TABLE command is used to DROP a single table and if that command fails then the query cannot generate 'partial results'. In that case the query will not be written to the binary log. --- .../suite/rpl/r/rpl_failed_drop_tbl_binlog.result | 42 +++++++++++ .../suite/rpl/t/rpl_failed_drop_tbl_binlog.test | 81 ++++++++++++++++++++++ sql/sql_table.cc | 8 ++- 3 files changed, 130 insertions(+), 1 deletion(-) diff --git a/mysql-test/suite/rpl/r/rpl_failed_drop_tbl_binlog.result b/mysql-test/suite/rpl/r/rpl_failed_drop_tbl_binlog.result new file mode 100644 index 00000000000..de166047bed --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_failed_drop_tbl_binlog.result @@ -0,0 +1,42 @@ +include/master-slave.inc +[connection master] +connect master2,localhost,root,,; +connection master; +CREATE TABLE t1 (A INT); +connection slave; +connection master; +LOCK TABLE t1 WRITE; +SET debug_sync='rm_table_no_locks_before_delete_table WAIT_FOR go'; +DROP TABLE IF EXISTS t1; +connection master1; +SET debug_sync='open_tables_after_open_and_process_table SIGNAL go WAIT_FOR go2'; +PREPARE x FROM "INSERT t1 VALUES (10)"; +connection master2; +KILL QUERY 9; +SET debug_sync='now SIGNAL go2'; +connection master; +ERROR 70100: Query execution was interrupted +include/assert.inc [Drop with single table should not be written to the binary log if the query execution fails] +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (A INT) +disconnect master2; +"Table t1 should be listed" +SHOW TABLES; +Tables_in_test +t1 +connection master; +UNLOCK TABLES; +SET debug_sync='reset'; +# DROP TABLE command with multiple tables fails the command should be written into the binary log. +DROP TABLE t1,t2; +ERROR 42S02: Unknown table 'test.t2' +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (A INT) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server */ +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_failed_drop_tbl_binlog.test b/mysql-test/suite/rpl/t/rpl_failed_drop_tbl_binlog.test new file mode 100644 index 00000000000..e8eaeb493c7 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_failed_drop_tbl_binlog.test @@ -0,0 +1,81 @@ +# ==== Purpose ==== +# +# Check that when the execution of a DROP TABLE command with single table +# fails it should not be written to the binary log. Also test that when the +# execution of DROP TABLE command with multiple tables fails the command +# should be written into the binary log. +# +# ==== Implementation ==== +# +# Steps: +# 0 - Create table named t1. +# 1 - Simulate a scenario where DROP TABLE command waits for an exclusive +# MDL lock to drop the table. +# 2 - Kill the query from another session. +# 3 - The DROP TABLE query execution will be interrupted. Ensure that the +# failed DROP TABLE command is not written to the binary log. +# 4 - Execute DROP TABLE t1, t2 statement. It will fail as 't2' table is +# not present. It should error out with unknown table error. +# 5 - The command should be written to binary log as it has already dropped +# table t1. +# +# ==== References ==== +# +# MENT-325: DROP TABLE IF EXISTS killed on master but was replicated. +# + +--source include/have_debug_sync.inc +--source include/have_binlog_format_statement.inc +--source include/master-slave.inc + +connect (master2,localhost,root,,); + +--connection master +CREATE TABLE t1 (A INT); +# Save master position +--let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1) +--sync_slave_with_master + +--connection master +--let id=`select connection_id()` +LOCK TABLE t1 WRITE; +SET debug_sync='rm_table_no_locks_before_delete_table WAIT_FOR go'; +--send DROP TABLE IF EXISTS t1 + +--connection master1 +--let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE like "debug sync point: rm_table_no_locks_before_delete_table%" +--source include/wait_condition.inc +SET debug_sync='open_tables_after_open_and_process_table SIGNAL go WAIT_FOR go2'; +--send PREPARE x FROM "INSERT t1 VALUES (10)" + +--connection master2 +--let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE like "debug sync point: open_tables_after_open_and_process_table%" +--source include/wait_condition.inc +eval KILL QUERY $id; +SET debug_sync='now SIGNAL go2'; + +--connection master +--error ER_QUERY_INTERRUPTED +reap; + +--let $current_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1) +--let $assert_text= Drop with single table should not be written to the binary log if the query execution fails +--let $assert_cond= $current_master_pos = $saved_master_pos +--source include/assert.inc + +--source include/show_binlog_events.inc +disconnect master2; + +--echo "Table t1 should be listed" +SHOW TABLES; + +--connection master +UNLOCK TABLES; +SET debug_sync='reset'; +--echo # DROP TABLE command with multiple tables fails the command should be written into the binary log. +--error ER_BAD_TABLE_ERROR +DROP TABLE t1,t2; +--source include/show_binlog_events.inc +--sync_slave_with_master + +--source include/rpl_end.inc diff --git a/sql/sql_table.cc b/sql/sql_table.cc index fbd6622b1b2..65ac14f8541 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2586,7 +2586,13 @@ int mysql_rm_table_no_locks(THD *thd, TABLE_LIST *tables, bool if_exists, is_drop_tmp_if_exists_added, 0); } - if (non_tmp_table_deleted) + /* + When the DROP TABLE command is used to drop a single table and if that + command fails then the query cannot generate 'partial results'. In that + case the query will not be written to the binary log. + */ + if (non_tmp_table_deleted && + (thd->lex->select_lex.table_list.elements > 1 || !error)) { /* Chop of the last comma */ built_query.chop();

1 0

[Commits] d9ccaadf138: MDEV-18791 Wrong error upon creating Aria table with long index on BLOB
by sachin.setiya＠mariadb.com 08 Aug '19

08 Aug '19

revision-id: d9ccaadf1382e257412234916b1cb17cc94c79f8 (mariadb-10.4.5-154-gd9ccaadf138) parent(s): c776ed4d6ecf9a0393a8eb53cd6660ff3eb63758 author: Sachin committer: Sachin timestamp: 2019-08-08 21:20:05 +0530 message: MDEV-18791 Wrong error upon creating Aria table with long index on BLOB If we have long unique key for aria engine return a too long key error --- mysql-test/main/long_unique_bugs.result | 2 ++ mysql-test/main/long_unique_bugs.test | 6 ++++++ storage/maria/ha_maria.cc | 5 +++++ 3 files changed, 13 insertions(+) diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 4e831bcff92..c0921e6cceb 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -271,3 +271,5 @@ drop table t1; CREATE TABLE t1 (a BLOB, UNIQUE(a)) ENGINE=MyISAM; INSERT DELAYED t1 () VALUES (); DROP TABLE t1; +CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria; +ERROR 42000: Specified key was too long; max key length is 1000 bytes diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index b9b10b3d6cd..c998f8660da 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -342,3 +342,9 @@ CREATE TABLE t1 (a BLOB, UNIQUE(a)) ENGINE=MyISAM; INSERT DELAYED t1 () VALUES (); # Cleanup DROP TABLE t1; + +# +# MDEV-18791 Wrong error upon creating Aria table with long index on BLOB +# +--error ER_TOO_LONG_KEY +CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria; diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc index 3edd642462f..ffeeab9e8c7 100644 --- a/storage/maria/ha_maria.cc +++ b/storage/maria/ha_maria.cc @@ -554,6 +554,11 @@ static int table2maria(TABLE *table_arg, data_file_type row_type, if (!table_arg->field[field->field_index]->stored_in_db()) { my_free(*recinfo_out); + if (table_arg->s->long_unique_table) + { + my_error(ER_TOO_LONG_KEY, MYF(0), table_arg->file->max_key_length()); + DBUG_RETURN(HA_ERR_INDEX_COL_TOO_LONG); + } my_error(ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN, MYF(0)); DBUG_RETURN(HA_ERR_UNSUPPORTED); }

1 0

[Commits] f36c0189b15: MDEV-17544: No warning when trying to name a primary key constraint
by Sergei Petrunia 06 Aug '19

06 Aug '19

revision-id: f36c0189b15b7ba0bd2de187223fe59f2359186e (mariadb-10.3.17-6-gf36c0189b15) parent(s): 4c4e379ba983553ea4df07eb1fe90745d173a866 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-08-06 14:37:08 +0300 message: MDEV-17544: No warning when trying to name a primary key constraint Part#2: update .result files for affected MyRocks tests --- .../mysql-test/rocksdb/r/add_unique_index_inplace.result | 8 ++++++++ .../mysql-test/rocksdb/r/type_blob_indexes.result | 10 ++++++++++ .../mysql-test/rocksdb/r/type_text_indexes.result | 16 ++++++++++++++++ 3 files changed, 34 insertions(+) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/add_unique_index_inplace.result b/storage/rocksdb/mysql-test/rocksdb/r/add_unique_index_inplace.result index f7c4bab685d..e998cfb43c1 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/add_unique_index_inplace.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/add_unique_index_inplace.result @@ -1,5 +1,7 @@ drop table if exists t1; CREATE TABLE t1 (a INT, b INT, PRIMARY KEY ka(a)) ENGINE=RocksDB; +Warnings: +Warning 1280 Name 'ka' ignored for PRIMARY key. INSERT INTO t1 (a, b) VALUES (1, 5); INSERT INTO t1 (a, b) VALUES (2, 6); INSERT INTO t1 (a, b) VALUES (3, 7); @@ -15,6 +17,8 @@ t1 CREATE TABLE `t1` ( ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, PRIMARY KEY ka(a)) ENGINE=RocksDB; +Warnings: +Warning 1280 Name 'ka' ignored for PRIMARY key. INSERT INTO t1 (a, b) VALUES (1, 5); INSERT INTO t1 (a, b) VALUES (2, 6); INSERT INTO t1 (a, b) VALUES (3, 7); @@ -38,6 +42,8 @@ a b 5 8 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, PRIMARY KEY ka(a)) ENGINE=RocksDB; +Warnings: +Warning 1280 Name 'ka' ignored for PRIMARY key. INSERT INTO t1 (a, b) VALUES (1, 5); INSERT INTO t1 (a, b) VALUES (2, NULL); INSERT INTO t1 (a, b) VALUES (3, NULL); @@ -56,6 +62,8 @@ COUNT(*) 4 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY ka(a)) ENGINE=RocksDB; +Warnings: +Warning 1280 Name 'ka' ignored for PRIMARY key. INSERT INTO t1 (a,b,c) VALUES (1,1,NULL); INSERT INTO t1 (a,b,c) VALUES (2,1,NULL); INSERT INTO t1 (a,b,c) VALUES (3,1,NULL); diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result index 26726e0f6d1..1e614a2bbf1 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_blob_indexes.result @@ -8,6 +8,8 @@ m MEDIUMBLOB, l LONGBLOB, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. SHOW INDEX IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 b A 1000 32 NULL LSMTREE @@ -133,6 +135,8 @@ CREATE TABLE t1 ( b BLOB, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), @@ -147,6 +151,8 @@ CREATE TABLE t1 ( b TINYBLOB, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), @@ -161,6 +167,8 @@ CREATE TABLE t1 ( b MEDIUMBLOB, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), @@ -175,6 +183,8 @@ CREATE TABLE t1 ( b LONGBLOB, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result index 22318316596..7db5c23c53f 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_text_indexes.result @@ -8,6 +8,8 @@ m MEDIUMTEXT, l LONGTEXT, PRIMARY KEY t (t(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 't' ignored for PRIMARY key. SHOW INDEX IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 t A 1000 32 NULL LSMTREE @@ -55,6 +57,8 @@ pk MEDIUMTEXT, PRIMARY KEY mt (pk(1)), INDEX (m(128)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'mt' ignored for PRIMARY key. SHOW INDEX IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 pk A 1000 1 NULL LSMTREE @@ -80,6 +84,8 @@ CREATE TABLE t1 ( b TEXT, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), @@ -94,6 +100,8 @@ CREATE TABLE t1 ( b TINYTEXT, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), @@ -108,6 +116,8 @@ CREATE TABLE t1 ( b MEDIUMTEXT, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), @@ -122,6 +132,8 @@ CREATE TABLE t1 ( b LONGTEXT, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), @@ -136,6 +148,8 @@ CREATE TABLE t1 ( b LONGTEXT CHARACTER SET "binary" COLLATE "binary", PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), @@ -154,6 +168,8 @@ CREATE TABLE t1 ( b LONGTEXT CHARACTER SET "latin1" COLLATE "latin1_bin", PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; +Warnings: +Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES (''), (_binary 0x0), (' '); ERROR 23000: Duplicate entry ' ' for key 'PRIMARY' INSERT INTO t1 (b) VALUES (''), (_binary 0x0);

1 0

[Commits] 97d522eeeaf: MDEV-20217: Semi_sync: Last_IO_Error: Fatal error: Failed to run 'after_queue_event' hook
by sujatha 05 Aug '19

05 Aug '19

revision-id: 97d522eeeaf3ff3b2d75c3009f86470c87f8b1aa (mariadb-10.3.17-4-g97d522eeeaf) parent(s): e1e142e7fca77f24a26a789fd6116896391e852a author: Sujatha committer: Sujatha timestamp: 2019-08-05 12:28:43 +0530 message: MDEV-20217: Semi_sync: Last_IO_Error: Fatal error: Failed to run 'after_queue_event' hook Fix: === Implemented upstream fix. commit 7d3d0fc303183ef50a343680ce70df71d5675cd1 Author: He Zhenxing <zhenxing.he(a)sun.com> Backport Bug#45852 Semisynch: Last_IO_Error: Fatal error: Failed to run 'after_queue_event' hook Errors when send reply to master should never cause the IO thread to stop, because master can fall back to async replication if it does not get reply from slave. The problem is fixed by deliberately ignoring the return value of slave_reply. --- .../rpl/r/rpl_semi_sync_slave_reply_fail.result | 40 ++++++++++ .../rpl/t/rpl_semi_sync_slave_reply_fail.test | 87 ++++++++++++++++++++++ sql/slave.cc | 13 ++-- 3 files changed, 134 insertions(+), 6 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync_slave_reply_fail.result b/mysql-test/suite/rpl/r/rpl_semi_sync_slave_reply_fail.result new file mode 100644 index 00000000000..6b39b296cdf --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_semi_sync_slave_reply_fail.result @@ -0,0 +1,40 @@ +include/master-slave.inc +[connection master] +connection slave; +include/stop_slave.inc +connection master; +call mtr.add_suppression("Timeout waiting for reply of binlog*"); +set global rpl_semi_sync_master_enabled = ON; +SET @@GLOBAL.rpl_semi_sync_master_timeout=100; +create table t1 (i int); +connection slave; +set global rpl_semi_sync_slave_enabled = ON; +CALL mtr.add_suppression("Semi-sync slave net_flush*"); +SET @save_debug= @@global.debug; +SET GLOBAL debug_dbug="+d,semislave_failed_net_flush"; +include/start_slave.inc +connection master; +connection slave; +"Assert that the net_fulsh() reply failed is present in slave error log. +FOUND 1 /Semi-sync slave net_flush reply failed/ in mysqld.2.err +"Assert that Slave IO thread is up and running." +SHOW STATUS LIKE 'Slave_running'; +Variable_name Value +Slave_running ON +Slave_IO_Running= Yes +"Clear the network failure simulation." +SET GLOBAL debug_dbug= @save_debug; +connection master; +insert into t1 values (10); +connection slave; +connection slave; +# Compare the tables on master and slave. +include/diff_tables.inc [master:t1, slave:t1] +connection master; +drop table t1; +connection slave; +set global rpl_semi_sync_slave_enabled = OFF; +connection master; +set global rpl_semi_sync_master_enabled = OFF; +SET @@GLOBAL.rpl_semi_sync_master_timeout = 10000; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_slave_reply_fail.test b/mysql-test/suite/rpl/t/rpl_semi_sync_slave_reply_fail.test new file mode 100644 index 00000000000..f0eb474f00e --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_slave_reply_fail.test @@ -0,0 +1,87 @@ +# ==== Purpose ==== +# +# Test verifies that slave IO thread doesn't report an error, when slave fails +# to send an acknowledgment to master with semi sync replication in use. +# +# ==== Implementation ==== +# +# Steps: +# 0 - Have semi synchronous replication in use. +# 1 - Enable a debug simulation point which simulates network flush failure +# at the time of slave reply operation. +# 2 - Do some operation on master and wait for it to be replicated. Master +# will timeout waiting for reply from slave. +# 3 - Check the slave error log for appropriate error message regarding +# net_flush operation failure. +# 4 - Remove the debug simulation and do some more DML operations on master +# and wait for them to be replicated. +# 5 - Slave will be able to replicate and data is consistent on both master +# and slave. Semi sync will be automatically turned on. +# +# ==== References ==== +# +# MDEV-20217: Semi_sync: Last_IO_Error: Fatal error: Failed to run +# 'after_queue_event' hook +# +--source include/have_debug.inc +--source include/master-slave.inc + +--connection slave +--source include/stop_slave.inc + +--connection master +call mtr.add_suppression("Timeout waiting for reply of binlog*"); +--let $sav_timeout_master=`SELECT @@GLOBAL.rpl_semi_sync_master_timeout` +set global rpl_semi_sync_master_enabled = ON; +SET @@GLOBAL.rpl_semi_sync_master_timeout=100; +create table t1 (i int); + +--connection slave +set global rpl_semi_sync_slave_enabled = ON; +CALL mtr.add_suppression("Semi-sync slave net_flush*"); +SET @save_debug= @@global.debug; +SET GLOBAL debug_dbug="+d,semislave_failed_net_flush"; +--source include/start_slave.inc + +--connection master +--sync_slave_with_master + +# Check error log for correct messages. +let $log_error_= `SELECT @@GLOBAL.log_error`; +if(!$log_error_) +{ + # MySQL Server on windows is started with --console and thus + # does not know the location of its .err log, use default location + let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.2.err; +} +--echo "Assert that the net_fulsh() reply failed is present in slave error log. +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=Semi-sync slave net_flush reply failed +--source include/search_pattern_in_file.inc + +--echo "Assert that Slave IO thread is up and running." +SHOW STATUS LIKE 'Slave_running'; +let $status= query_get_value("show slave status", Slave_IO_Running, 1); +echo Slave_IO_Running= $status; + +--echo "Clear the network failure simulation." +SET GLOBAL debug_dbug= @save_debug; + +--connection master +insert into t1 values (10); +--sync_slave_with_master + +--connection slave +--echo # Compare the tables on master and slave. +--let $diff_tables= master:t1, slave:t1 +--source include/diff_tables.inc + +--connection master +drop table t1; +--sync_slave_with_master +set global rpl_semi_sync_slave_enabled = OFF; + +--connection master +set global rpl_semi_sync_master_enabled = OFF; +--eval SET @@GLOBAL.rpl_semi_sync_master_timeout = $sav_timeout_master +--source include/rpl_end.inc diff --git a/sql/slave.cc b/sql/slave.cc index 4007f323a6e..165aa20b1ee 100644 --- a/sql/slave.cc +++ b/sql/slave.cc @@ -4911,13 +4911,14 @@ Stopping slave I/O thread due to out-of-memory error from master"); goto err; } - if (rpl_semi_sync_slave_status && (mi->semi_ack & SEMI_SYNC_NEED_ACK) && - repl_semisync_slave.slave_reply(mi)) + if (rpl_semi_sync_slave_status && (mi->semi_ack & SEMI_SYNC_NEED_ACK)) { - mi->report(ERROR_LEVEL, ER_SLAVE_FATAL_ERROR, NULL, - ER_THD(thd, ER_SLAVE_FATAL_ERROR), - "Failed to run 'after_queue_event' hook"); - goto err; + /* + We deliberately ignore the error in slave_reply, such error should + not cause the slave IO thread to stop, and the error messages are + already reported. + */ + (void)repl_semisync_slave.slave_reply(mi); } if (mi->using_gtid == Master_info::USE_GTID_NO &&

1 0

[Commits] 988ff90256d: MDEV-20227: rocksdb.rocksdb_concurrent_delete fails on windows
by Sergei Petrunia 04 Aug '19

04 Aug '19

revision-id: 988ff90256d2aacbcb6aaf57fc8984dc60ae74ff (mariadb-10.2.26-3-g988ff90256d) parent(s): 09a85692a65634cda40214b6693f34bec2f5b73b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-08-04 23:37:47 +0300 message: MDEV-20227: rocksdb.rocksdb_concurrent_delete fails on windows A combination of: * lots of include'd test files where each has "--source include/have_rocksdb.inc" * for each such occurrence, MTR adds testsuite's arguments into server arguments * which hits some limit on the length of argv array on Windows, causing the server to get garbage data in the last argument. Work around this by commenting out one of the totally redundant "source include/have_rocksdb.inc" lines. --- storage/rocksdb/mysql-test/rocksdb/t/rocksdb_concurrent_delete.inc | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) diff --git a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_concurrent_delete.inc b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_concurrent_delete.inc index 55f466a4d31..5336c77ee83 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_concurrent_delete.inc +++ b/storage/rocksdb/mysql-test/rocksdb/t/rocksdb_concurrent_delete.inc @@ -1,4 +1,7 @@ ---source include/have_rocksdb.inc +# MariaDB: including the below too many times causes really long argv list +# in win_main()'s argument which blows up some limit on Windows. +# Comment it out: +#--source include/have_rocksdb.inc --source include/have_debug_sync.inc --source include/count_sessions.inc

1 0

[Commits] 638fa95e6b4: Post-merge fixes for rocksdb.group_min_max test
by Sergei Petrunia 03 Aug '19

03 Aug '19

revision-id: 638fa95e6b4a3ea4ffd6d776f49096b61c84ab0f (mariadb-10.2.25-87-g638fa95e6b4) parent(s): 731ef751758e3267745bfce582a3692115753289 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-08-03 23:15:44 +0300 message: Post-merge fixes for rocksdb.group_min_max test - Fix the LooseScan code to support storage engines that return HA_ERR_END_OF_FILE if the index scan goes out of provided range bounds - Add a DBUG_EXECUTE_IF("force_group_by",...) to allow a test to force a LooseScan - Adjust rocksdb.group_min_max test not to use features not present in MariaDB 10.2 (e.g. optimizer_trace. In MariaDB 10.4 it's present but it doesn't meet the assumptions that the test makes about it - Adjust the test result file: = MariaDB doesn't support "Enhanced Loose Scan" that FB/MySQL has = MariaDB has different cost calculations. --- sql/opt_range.cc | 38 +- .../mysql-test/rocksdb/include/group_min_max.inc | 49 +- .../mysql-test/rocksdb/r/group_min_max.result | 663 +++++++++++---------- .../mysql-test/rocksdb/t/group_min_max.test | 5 +- 4 files changed, 395 insertions(+), 360 deletions(-) diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 3ba8c44d790..3f6c18dca5c 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2428,6 +2428,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, KEY_PART *key_parts; KEY *key_info; PARAM param; + bool force_group_by = false; if (check_stack_overrun(thd, 2*STACK_MIN_SIZE + sizeof(PARAM), buff)) DBUG_RETURN(0); // Fatal error flag is set @@ -2555,15 +2556,20 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, Try to construct a QUICK_GROUP_MIN_MAX_SELECT. Notice that it can be constructed no matter if there is a range tree. */ + DBUG_EXECUTE_IF("force_group_by", force_group_by = true; ); group_trp= get_best_group_min_max(&param, tree, best_read_time); if (group_trp) { param.table->quick_condition_rows= MY_MIN(group_trp->records, head->stat_records()); - if (group_trp->read_cost < best_read_time) + if (group_trp->read_cost < best_read_time || force_group_by) { best_trp= group_trp; best_read_time= best_trp->read_cost; + if (force_group_by) + { + goto force_plan; + } } } @@ -2663,6 +2669,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, } } +force_plan: thd->mem_root= param.old_root; /* If we got a read plan, create a quick select from it. */ @@ -11509,13 +11516,28 @@ int QUICK_RANGE_SELECT::get_next_prefix(uint prefix_length, DBUG_ASSERT(cur_prefix != NULL); result= file->ha_index_read_map(record, cur_prefix, keypart_map, HA_READ_AFTER_KEY); - if (result || last_range->max_keypart_map == 0) - DBUG_RETURN(result); - - key_range previous_endpoint; - last_range->make_max_endpoint(&previous_endpoint, prefix_length, keypart_map); - if (file->compare_key(&previous_endpoint) <= 0) - DBUG_RETURN(0); + if (result || last_range->max_keypart_map == 0) { + /* + Only return if actual failure occurred. For HA_ERR_KEY_NOT_FOUND + or HA_ERR_END_OF_FILE, we just want to continue to reach the next + set of ranges. It is possible for the storage engine to return + HA_ERR_KEY_NOT_FOUND/HA_ERR_END_OF_FILE even when there are more + keys if it respects the end range set by the read_range_first call + below. + */ + if (result != HA_ERR_KEY_NOT_FOUND && result != HA_ERR_END_OF_FILE) + DBUG_RETURN(result); + } else { + /* + For storage engines that don't respect end range, check if we've + moved past the current range. + */ + key_range previous_endpoint; + last_range->make_max_endpoint(&previous_endpoint, prefix_length, + keypart_map); + if (file->compare_key(&previous_endpoint) <= 0) + DBUG_RETURN(0); + } } uint count= ranges.elements - (uint)(cur_range - (QUICK_RANGE**) ranges.buffer); diff --git a/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc b/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc index 79ac367a73b..6c3e29537b1 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/group_min_max.inc @@ -266,6 +266,17 @@ select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; # B) Equalities only over the non-group 'B' attributes # plans +--echo # +--echo # MariaDB: we dont have the following patch: +--echo # +--echo # commit 60a92a79a3b7fde3c6efe91799e344b977c8e5c3 +--echo # Author: Manuel Ung <mung(a)fb.com> +--echo # Date: Thu Apr 19 23:06:27 2018 -0700 +--echo # +--echo # Enhance group-by loose index scan +--echo # +--echo # So the following results are not very meaningful, but are still kept here + explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; @@ -730,9 +741,9 @@ explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2, explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; # test multi_range_groupby flag -set optimizer_switch = 'multi_range_groupby=off'; +#MariaDB: no support: set optimizer_switch = 'multi_range_groupby=off'; explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; -set optimizer_switch = 'default'; +#set optimizer_switch = 'default'; explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; @@ -1361,41 +1372,41 @@ drop table t1; eval CREATE TABLE t (a INT, b INT, KEY(a,b)) engine=$engine; INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5); ANALYZE TABLE t; -let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB -eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE; -set @@session.optimizer_trace='enabled=on'; -set end_markers_in_json=on; +# MariaDB: 10.2 doesn't have trace, yet: let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB +# eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE; +# set @@session.optimizer_trace='enabled=on'; +# set end_markers_in_json=on; ANALYZE TABLE t; SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; -SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK - FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; +#SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK +# FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; -SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK - FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; +#SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK +# FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); -SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK - FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; +#SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK +# FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; -SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK - FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; +#SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK +# FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; -SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK - FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; +#SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK +# FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; -SET optimizer_trace_max_mem_size=DEFAULT; -SET optimizer_trace=DEFAULT; -SET end_markers_in_json=DEFAULT; +#SET optimizer_trace_max_mem_size=DEFAULT; +#SET optimizer_trace=DEFAULT; +#SET end_markers_in_json=DEFAULT; DROP TABLE t; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/group_min_max.result b/storage/rocksdb/mysql-test/rocksdb/r/group_min_max.result index 7fede0ac603..a1031f518e0 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/group_min_max.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/group_min_max.result @@ -1,4 +1,5 @@ -set global debug="+d,force_group_by"; +set @debug_tmp= @@debug_dbug; +set global debug_dbug="+d,force_group_by"; drop table if exists t1; create table t1 ( a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' ' @@ -134,34 +135,34 @@ Table Op Msg_type Msg_text test.t3 analyze status OK explain select a1, min(a2) from t1 group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 126 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using index for group-by explain select a1, max(a2) from t1 group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 65 NULL 126 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 65 NULL 63 Using index for group-by explain select a1, min(a2), max(a2) from t1 group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 126 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using index for group-by explain select a1, a2, b, min(c), max(c) from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by explain select a1,a2,b,max(c),min(c) from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by explain select a1,a2,b,max(c),min(c) from t2 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 # NULL # Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 # NULL # Using index for group-by explain select min(a2), a1, max(a2), min(a2), a1 from t1 group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 126 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using index for group-by explain select a1, b, min(c), a1, max(c), b, a2, max(c), max(c) from t1 group by a1, a2, b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by explain select min(a2) from t1 group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 126 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using index for group-by explain select a2, min(c), max(c) from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by select a1, min(a2) from t1 group by a1; a1 min(a2) a a @@ -288,37 +289,37 @@ b i421 l421 b m422 p422 explain select a1,a2,b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1002 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 502 Using where; Using index for group-by explain select a1, max(c) from t1 where a1 >= 'c' or a1 < 'b' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1002 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 502 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where a1 >= 'c' or a2 < 'b' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1503 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 753 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where a1 = 'z' or a1 = 'b' or a1 = 'd' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1503 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 753 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 2004 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1004 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 2004 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1004 Using where; Using index for group-by explain select a1,min(c),max(c) from t1 where a1 >= 'b' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1, max(c) from t1 where a1 in ('a','b','d') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 1503 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 753 Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where a1 < 'd' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by @@ -668,99 +669,109 @@ d d411 d h412 d l421 d p422 +# +# MariaDB: we dont have the following patch: +# +# commit 60a92a79a3b7fde3c6efe91799e344b977c8e5c3 +# Author: Manuel Ung <mung(a)fb.com> +# Date: Thu Apr 19 23:06:27 2018 -0700 +# +# Enhance group-by loose index scan +# +# So the following results are not very meaningful, but are still kept here explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 126 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 63 Using where; Using index for group-by explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 126 Using where; Using index for group-by +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 126 Using where; Using index for group-by +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 126 Using where; Using index for group-by +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 126 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 63 Using where; Using index for group-by explain select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 126 Using where; Using index for group-by +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 126 Using where; Using index for group-by +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 126 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 126 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 126 Using where; Using index for group-by explain select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 63 Using where; Using index for group-by explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 63 Using where; Using index for group-by explain select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 251 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 126 Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 251 Using where; Using index for group-by +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 251 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 126 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 251 Using where; Using index for group-by +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 251 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 126 Using where; Using index for group-by explain select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 251 Using where; Using index for group-by +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 126 Using where; Using index for group-by +1 SIMPLE t3 range NULL idx_t3_1 6 NULL 63 Using where; Using index for group-by explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 126 Using where; Using index for group-by +1 SIMPLE t3 index NULL idx_t3_1 10 NULL 1000 Using where; Using index explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 126 Using where; Using index for group-by +1 SIMPLE t3 index NULL idx_t3_1 10 NULL 1000 Using where; Using index explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 126 Using where; Using index for group-by +1 SIMPLE t3 range NULL idx_t3_1 6 NULL 63 Using where; Using index for group-by explain select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 126 Using where; Using index for group-by +1 SIMPLE t3 index NULL idx_t3_1 10 NULL 1000 Using where; Using index explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 126 Using where; Using index for group-by +1 SIMPLE t3 index NULL idx_t3_1 10 NULL 1000 Using where; Using index select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; a1 a2 b max(c) min(c) a a b h112 e112 @@ -769,22 +780,22 @@ c a b h312 e312 d a b h412 e412 select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; a1 a2 b max(c) min(c) -a b b p122 e112 -b b b p222 e212 -c b b p322 e312 -d b b p422 e412 +a a b p122 e112 +b a b p222 e212 +c a b p322 e312 +d a b p422 e412 select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; a1 a2 b max(c) min(c) -a a b h112 a111 -b a b h212 a211 -c a b h312 a311 -d a b h412 a411 +a a a h112 a111 +b a a h212 a211 +c a a h312 a311 +d a a h412 a411 select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1; a1 a2 b max(c) min(c) -a b b p122 a111 -b b b p222 a211 -c b b p322 a311 -d b b p422 a411 +a a a p122 a111 +b a a p222 a211 +c a a p322 a311 +d a a p422 a411 select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; a1 max(c) min(c) a h112 e112 @@ -815,14 +826,14 @@ d a b h412 d b b p422 select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; a1 a2 b max(c) -a a b h112 -a b b p122 -b a b h212 -b b b p222 -c a b h312 -c b b p322 -d a b h412 -d b b p422 +a a a h112 +a b a p122 +b a a h212 +b b a p222 +c a a h312 +c b a p322 +d a a h412 +d b a p422 select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; a1 a2 b min(c) max(c) a a b e112 h112 @@ -835,14 +846,14 @@ d a b e412 h412 d b b m422 p422 select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; a1 a2 b min(c) max(c) -a a b a111 h112 -a b b i121 p122 -b a b a211 h212 -b b b i221 p222 -c a b a311 h312 -c b b i321 p322 -d a b a411 h412 -d b b i421 p422 +a a a a111 h112 +a b a i121 p122 +b a a a211 h212 +b b a i221 p222 +c a a a311 h312 +c b a i321 p322 +d a a a411 h412 +d b a i421 p422 select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; a1 a2 max(c) a a h112 @@ -872,17 +883,18 @@ d a b h412 e412 e a b NULL NULL select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; a1 a2 b max(c) min(c) -a b b p122 e112 -b b b p222 e212 -c b b p322 e312 -d b b p422 e412 +a a b p122 e112 +b a b p222 e212 +c a b p322 e312 +d a b p422 e412 +e a b NULL NULL select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; a1 a2 b max(c) min(c) -a a b h112 a111 -b a b h212 a211 -c a b h312 a311 -d a b h412 a411 -e a b NULL NULL +a a a h112 a111 +b a a h212 a211 +c a a h312 a311 +d a a h412 a411 +e a a NULL NULL select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; a1 max(c) min(c) a h112 e112 @@ -896,6 +908,7 @@ a p122 e112 b p222 e212 c p322 e312 d p422 e412 +e NULL NULL select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; a1 max(c) min(c) a h112 a111 @@ -916,15 +929,15 @@ d b b p422 e a b NULL select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; a1 a2 b max(c) -a a b h112 -a b b p122 -b a b h212 -b b b p222 -c a b h312 -c b b p322 -d a b h412 -d b b p422 -e a b NULL +a a a h112 +a b a p122 +b a a h212 +b b a p222 +c a a h312 +c b a p322 +d a a h412 +d b a p422 +e a a NULL select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; a1 a2 b min(c) max(c) a a b e112 h112 @@ -938,15 +951,15 @@ d b b m422 p422 e a b NULL NULL select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; a1 a2 b min(c) max(c) -a a b a111 h112 -a b b i121 p122 -b a b a211 h212 -b b b i221 p222 -c a b a311 h312 -c b b i321 p322 -d a b a411 h412 -d b b i421 p422 -e a b NULL NULL +a a a a111 h112 +a b a i121 p122 +b a a a211 h212 +b b a i221 p222 +c a a a311 h312 +c b a i321 p322 +d a a a411 h412 +d b a i421 p422 +e a a NULL NULL select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; a1 a2 max(c) a a h112 @@ -976,14 +989,14 @@ b a b h212 e212 c a b h312 e312 select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; a1 a2 b max(c) min(c) -a b b p122 e112 -b b b p222 e212 -c b b p322 e312 +a a b p122 e112 +b a b p222 e212 +c a b p322 e312 select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; a1 a2 b max(c) min(c) -a a b h112 a111 -b a b h212 a211 -c a b h312 a311 +a a a h112 a111 +b a a h212 a211 +c a a h312 a311 select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; a1 max(c) min(c) a h112 e112 @@ -1001,37 +1014,41 @@ b h212 a211 c h312 a311 explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 63 Using where; Using index for group-by explain select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 63 Using where; Using index for group-by explain select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 251 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 126 Using where; Using index for group-by explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 251 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL 126 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 251 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 126 Using where; Using index for group-by select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; a1 a2 b min(c) a a NULL a777 c a NULL c777 select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; a1 a2 b min(c) +a a NULL a777 +c a NULL c777 select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; a1 a2 b max(c) a a NULL a999 c a NULL c999 select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; a1 a2 b max(c) +a a NULL a999 +c a NULL c999 select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; a1 a2 b min(c) a a NULL a777 @@ -1050,97 +1067,97 @@ a a NULL a777 a999 c a NULL c777 c999 explain select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL # Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c > 'f123') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'f123') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c < 'a0') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c < 'k321') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'k321') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'a0') or (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c between 'b111' and 'g112') or (c between 'd000' and 'i110') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c > 'f123') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c > 'f123') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c < 'a0') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c < 'k321') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c < 'k321') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c < 'a0') or (c > 'b1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c < 'c5') or (c = 'g412') or (c = 'k421') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where ((c > 'b111') and (c <= 'g112')) or ((c > 'd000') and (c <= 'i110')) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by select a1,a2,b, max(c) from t1 where (c > 'b1') group by a1,a2,b; a1 a2 b max(c) a a a d111 @@ -1562,35 +1579,36 @@ explain select a1,a2,b,min(c),max(c) from t1 where exists ( select * from t2 where t2.c = t1.c ) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 1000 Using where; Using index -2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index +1 PRIMARY t1 index NULL idx_t1_1 163 NULL 1000 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func 1 +2 MATERIALIZED t2 index NULL idx_t2_1 163 NULL 1000 Using index explain select a1,a2,b,min(c),max(c) from t1 where exists ( select * from t2 where t2.c > 'b1' ) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using index for group-by +1 PRIMARY t1 index NULL idx_t1_1 163 NULL 1000 Using index 2 SUBQUERY t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (c > 'b111') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 1002 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 502 Using where; Using index for group-by explain select a1,a2,b,min(c) from t1 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122') or (c < 'h112') or (c = 'c111')) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 1002 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 502 Using where; Using index for group-by explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) > 194) and (b = 'c') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by @@ -1599,7 +1617,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (a2 >= 'b') and (b = 'a') and (c > 'b111') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c) from t2 where ((a1 > 'a') or (a1 < '9')) and ((a2 >= 'b') and (a2 < 'z')) and (b = 'a') and ((c < 'h112') or (c = 'j121') or (c > 'k121' and c < 'm122') or (c > 'o122')) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by @@ -1695,31 +1713,31 @@ select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') grou a1 a2 b min(c) explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by @@ -1770,50 +1788,50 @@ select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1 a1 a2 b explain select distinct a1,a2,b from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range idx_t1_1 idx_t1_1 163 NULL 1001 99.90 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 501 100.00 Using where; Using index for group-by Warnings: -Note 1003 /* select#1 */ select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`c` = 'i121') and (`test`.`t1`.`b` = 'a') and (`test`.`t1`.`a2` >= 'b')) +Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`b` = 'a' and `test`.`t1`.`c` = 'i121' and `test`.`t1`.`a2` >= 'b' explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select distinct b from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1000 Using where; Using index +1 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 Using where; Using index explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 252 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 126 Using where; Using index for group-by explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 252 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 126 Using where; Using index for group-by explain select distinct a1,a2,b from t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx_t2_1 idx_t2_1 163 NULL 1001 99.90 Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL 501 100.00 Using where; Using index for group-by Warnings: -Note 1003 /* select#1 */ select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 'i121') and (`test`.`t2`.`b` = 'a') and (`test`.`t2`.`a2` >= 'b')) +Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`b` = 'a' and `test`.`t2`.`c` = 'i121' and `test`.`t2`.`a2` >= 'b' explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by explain select distinct b from t2 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index idx_t2_1,idx_t2_2 idx_t2_2 146 NULL 1000 Using where; Using index +1 SIMPLE t2 index NULL idx_t2_2 146 NULL 1000 Using where; Using index explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 129 NULL 252 Using where; Using index for group-by +1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 129 NULL 126 Using where; Using index for group-by explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 129 NULL 252 Using where; Using index for group-by +1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 129 NULL 126 Using where; Using index for group-by select distinct a1,a2,b from t1; a1 a2 b a a a @@ -1942,40 +1960,40 @@ c e d e explain select distinct a1,a2,b from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using index for group-by explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 251 Using where; Using index for group-by explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by; Using temporary; Using filesort +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by; Using temporary; Using filesort explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'b' group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 252 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 126 Using where; Using index for group-by explain select distinct a1 from t1 where a1 in ('a', 'd') and a2 = 'e' group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 252 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 126 Using where; Using index for group-by explain select distinct a1,a2,b from t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by; Using temporary; Using filesort +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by; Using temporary; Using filesort explain select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'b' group by a1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 129 NULL # Using where; Using index for group-by @@ -2064,23 +2082,23 @@ select distinct a1 from t2 where a1 in ('a', 'd') and a2 = 'e' group by a1; a1 explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by (scanning) +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1 idx_t1_1 163 NULL 1001 Using where; Using index for group-by (scanning) +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 501 Using where; Using index for group-by (scanning) explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 100.00 Using where; Using index for group-by (scanning) +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 100.00 Using where; Using index for group-by Warnings: -Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`b` = 'c') and (`test`.`t1`.`a1` > 'a') and (`test`.`t1`.`a2` > 'a')) +Note 1003 select count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `count(distinct a1,a2,b)` from `test`.`t1` where `test`.`t1`.`b` = 'c' and `test`.`t1`.`a1` > 'a' and `test`.`t1`.`a2` > 'a' explain select count(distinct b) from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1000 Using where; Using index +1 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 Using where; Using index explain extended select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 100.00 Using where; Using index for group-by (scanning) +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 100.00 Using where; Using index for group-by Warnings: -Note 1003 /* select#1 */ select (98 + count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`)) AS `98 + count(distinct a1,a2,b)` from `test`.`t1` where ((`test`.`t1`.`a1` > 'a') and (`test`.`t1`.`a2` > 'a')) +Note 1003 select 98 + count(distinct `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`) AS `98 + count(distinct a1,a2,b)` from `test`.`t1` where `test`.`t1`.`a1` > 'a' and `test`.`t1`.`a2` > 'a' select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); count(distinct a1,a2,b) 4 @@ -2098,19 +2116,19 @@ select 98 + count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a'); 104 explain select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select concat(a1,min(c)),b from t1 where a1 < 'd' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select concat(a1,min(c)),b,max(c) from t1 where a1 < 'd' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select concat(a1,a2),b,min(c),max(c) from t1 where a1 < 'd' group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 501 Using where; Using index for group-by +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using where; Using index for group-by explain select concat(ord(min(b)),ord(max(b))),min(b),max(b) from t1 group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 251 Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 126 Using index for group-by select a1,a2,b, concat(min(c), max(c)) from t1 where a1 < 'd' group by a1,a2,b; a1 a2 b concat(min(c), max(c)) a a a a111d111 @@ -2179,85 +2197,83 @@ concat(ord(min(b)),ord(max(b))) min(b) max(b) 9798 a b explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1000 NULL +1 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 explain select a1,a2,b,d from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1000 NULL +1 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 explain extended select a1,a2,min(b),max(b) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 1000 100.00 Using where; Using index Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`c` > 'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2` explain extended select a1,a2,b,min(c),max(c) from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1000 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`d` > 'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`d` > 'xy2' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain extended select a1,a2,b,c from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 1000 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`d` > 'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`d` > 'xy2' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c` explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_0 65 NULL 1000 Using where +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b < 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_0 65 NULL 1000 Using where +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_0 65 NULL 1000 Using where +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b' and b >= 'a') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_0 65 NULL 1000 Using where +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 1000 100.00 Using where; Using index Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`c` > 'a111')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_0 65 NULL 1000 Using where +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; a1 a2 min(b) c a a a a111 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_0 65 NULL 1000 Using where +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,min(c),max(c) from t2 where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 1000 Using where; Using index +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index idx_t1_1 idx_t1_1 163 NULL 1000 NULL +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 1000 explain select a1,a2,count(a2) from t1 group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1000 Using index +1 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 Using index explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1000 100.00 Using where; Using index Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where `test`.`t1`.`a1` > 'a' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 1000 100.00 Using where; Using index Warnings: -Note 1003 /* select#1 */ select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` -set optimizer_switch = 'multi_range_groupby=off'; +Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where `test`.`t1`.`a1` > 'a' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_0 65 NULL 1000 Using where -set optimizer_switch = 'default'; +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 126 Using where; Using index for group-by +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 1000 Using where; Using index explain select distinct(a1) from t1 where ord(a2) = 98; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_0 65 NULL 1000 Using where +1 SIMPLE t1 index NULL idx_t1_2 147 NULL 1000 Using where; Using index select distinct(a1) from t1 where ord(a2) = 98; a1 a @@ -2266,7 +2282,7 @@ c d explain select a1 from t1 where a2 = 'b' group by a1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 126 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using where; Using index for group-by select a1 from t1 where a2 = 'b' group by a1; a1 a @@ -2275,7 +2291,7 @@ c d explain select distinct a1 from t1 where a2 = 'b'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 126 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 63 Using where; Using index for group-by select distinct a1 from t1 where a2 = 'b'; a1 a @@ -2385,7 +2401,7 @@ INSERT INTO t1 (a) VALUES ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 66 NULL 1001 Using index for group-by +1 SIMPLE t1 index NULL PRIMARY 66 NULL 1000 Using index SELECT DISTINCT a,a FROM t1 ORDER BY a; a a @@ -2477,7 +2493,7 @@ Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by FLUSH STATUS; SELECT max(b), a FROM t1 GROUP BY a; max(b) a @@ -2489,15 +2505,17 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by FLUSH STATUS; CREATE TABLE t2 engine=RocksDB SELECT max(b), a FROM t1 GROUP BY a; SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 FLUSH STATUS; SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b; max(b) a @@ -2509,6 +2527,7 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 FLUSH STATUS; (SELECT max(b), a FROM t1 GROUP BY a) UNION (SELECT max(b), a FROM t1 GROUP BY a); @@ -2521,50 +2540,52 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 16 Handler_read_next 0 +Handler_read_retry 0 EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION (SELECT max(b), a FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range a a 5 NULL 501 Using index for group-by -2 UNION t1 range a a 5 NULL 501 Using index for group-by -NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using temporary +1 PRIMARY t1 range NULL a 5 NULL 251 Using index for group-by +2 UNION t1 range NULL a 5 NULL 251 Using index for group-by +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 1000 Using index -2 SUBQUERY t1 range a a 5 NULL 501 Using index for group-by +2 SUBQUERY t1 range NULL a 5 NULL 251 Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 1000 Using index -2 SUBQUERY t1 range a a 5 NULL 501 Using index for group-by +2 SUBQUERY t1 index NULL a 10 NULL 1000 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 range a a 5 NULL 501 Using index for group-by +1 PRIMARY t1_outer index NULL a 10 NULL 1000 Using index +2 SUBQUERY t1 range NULL a 5 NULL 251 Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer index NULL a 10 NULL 1000 Using where; Using index -2 SUBQUERY t1 range a a 5 NULL 501 Using index for group-by +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 251 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 4 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 251 Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer range a a 5 NULL 501 Using index for group-by -2 SUBQUERY t1 range a a 5 NULL 501 Using index for group-by +1 PRIMARY t1_outer range NULL a 5 NULL 251 Using index for group-by +2 SUBQUERY t1 range NULL a 5 NULL 251 Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer2 index NULL a 10 NULL 1000 Using where; Using index -1 PRIMARY t1_outer1 ref a a 10 const,test.t1_outer2.b 1 Using where; Using index -2 SUBQUERY t1 range a a 5 NULL 501 Using index for group-by +1 PRIMARY t1_outer1 ref a a 5 const 4 Using where; Using index +1 PRIMARY t1_outer2 index NULL a 10 NULL 1000 Using where; Using index; Using join buffer (flat, BNL join) +2 SUBQUERY t1 range NULL a 5 NULL 251 Using index for group-by EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer2 index NULL a 10 NULL 1000 Using index 2 SUBQUERY t1_outer index NULL a 10 NULL 1000 Using index -3 SUBQUERY t1 range a a 5 NULL 501 Using index for group-by +3 SUBQUERY t1 range NULL a 5 NULL 251 Using index for group-by CREATE TABLE t3 LIKE t1; FLUSH STATUS; INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; @@ -2572,6 +2593,7 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 DELETE FROM t3; FLUSH STATUS; INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) @@ -2580,12 +2602,14 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 FLUSH STATUS; DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000; SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 FLUSH STATUS; DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1) > 10000; @@ -2594,6 +2618,7 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 1 +Handler_read_retry 0 DROP TABLE t1,t2,t3; CREATE TABLE t1 (a int, INDEX idx(a)) engine=RocksDB; INSERT INTO t1 VALUES @@ -2604,7 +2629,7 @@ Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN SELECT DISTINCT(a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx idx 5 NULL 1001 Using index for group-by +1 SIMPLE t1 range NULL idx 5 NULL 501 Using index for group-by SELECT DISTINCT(a) FROM t1; a 1 @@ -2612,7 +2637,7 @@ a 4 EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx idx 5 NULL 1001 Using index for group-by +1 SIMPLE t1 range NULL idx 5 NULL 501 Using index for group-by SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; a 1 @@ -2640,7 +2665,7 @@ CREATE INDEX break_it ON t1 (a, b); EXPLAIN SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range break_it break_it 10 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL break_it 10 NULL 251 Using index for group-by SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; a MIN(b) MAX(b) 1 1 3 @@ -2650,7 +2675,7 @@ a MIN(b) MAX(b) EXPLAIN SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range break_it break_it 10 NULL 501 Using index for group-by; Using temporary; Using filesort +1 SIMPLE t1 range NULL break_it 10 NULL 251 Using index for group-by; Using temporary; Using filesort SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; a MIN(b) MAX(b) 4 1 3 @@ -2660,7 +2685,7 @@ a MIN(b) MAX(b) EXPLAIN SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index break_it break_it 10 NULL 1000 Using index +1 SIMPLE t1 index NULL break_it 10 NULL 1000 Using index SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; a MIN(b) MAX(b) AVG(b) 4 1 3 2.0000 @@ -2743,9 +2768,9 @@ a b 3 13 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,index PRIMARY 4 NULL 1 100.00 Using where; Using index for group-by; Using temporary +1 SIMPLE t1 ref PRIMARY,index PRIMARY 4 const 15 100.00 Using index; Using temporary Warnings: -Note 1003 /* select#1 */ select sql_buffer_result `test`.`t1`.`a` AS `a`,(max(`test`.`t1`.`b`) + 1) AS `max(b)+1` from `test`.`t1` where (`test`.`t1`.`a` = 0) group by `test`.`t1`.`a` +Note 1003 select sql_buffer_result `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) + 1 AS `max(b)+1` from `test`.`t1` where `test`.`t1`.`a` = 0 group by `test`.`t1`.`a` drop table t1; CREATE TABLE t1 (a int, b int, c int, d int, KEY foo (c,d,a,b), KEY bar (c,a,b,d)) engine=RocksDB; @@ -2758,7 +2783,7 @@ Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range foo,bar foo 10 NULL 126 Using where; Using index for group-by +1 SIMPLE t1 range NULL foo 10 NULL 63 Using where; Using index for group-by SELECT DISTINCT c FROM t1 WHERE d=4; c 1 @@ -2780,7 +2805,7 @@ test.t analyze status OK EXPLAIN SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t range a a 10 NULL 501 Using where; Using index for group-by +1 SIMPLE t range NULL a 10 NULL 251 Using where; Using index for group-by #should return 1 row SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; a MIN(b) @@ -2790,7 +2815,7 @@ a MIN(b) EXPLAIN SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t range a a 10 NULL 501 Using where; Using index for group-by +1 SIMPLE t range NULL a 10 NULL 251 Using where; Using index for group-by #should return 1 row SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; a MAX(b) @@ -2801,7 +2826,7 @@ INSERT INTO t SELECT a, 2 FROM t; EXPLAIN SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t range a a 10 NULL 501 Using where; Using index for group-by +1 SIMPLE t range NULL a 10 NULL 251 Using where; Using index for group-by #should return 1 row SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; a MAX(b) @@ -2915,7 +2940,7 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) @@ -2990,7 +3015,7 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) @@ -3072,7 +3097,7 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) @@ -3119,45 +3144,45 @@ Table Op Msg_type Msg_text test.t2 analyze status OK EXPLAIN SELECT COUNT(DISTINCT a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by SELECT COUNT(DISTINCT a) FROM t1; COUNT(DISTINCT a) 2 EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 10 NULL 1001 Using index for group-by (scanning) +1 SIMPLE t1 range NULL a 10 NULL 501 Using index for group-by SELECT COUNT(DISTINCT a,b) FROM t1; COUNT(DISTINCT a,b) 16 EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 10 NULL 1001 Using index for group-by (scanning) +1 SIMPLE t1 range NULL a 10 NULL 501 Using index for group-by SELECT COUNT(DISTINCT b,a) FROM t1; COUNT(DISTINCT b,a) 16 EXPLAIN SELECT COUNT(DISTINCT b) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 10 NULL 1000 Using index +1 SIMPLE t1 index NULL a 10 NULL 1000 Using index SELECT COUNT(DISTINCT b) FROM t1; COUNT(DISTINCT b) 8 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by SELECT COUNT(DISTINCT a) FROM t1 GROUP BY a; COUNT(DISTINCT a) 1 1 EXPLAIN SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 10 NULL 1001 Using index for group-by (scanning) +1 SIMPLE t1 range NULL a 10 NULL 501 Using index for group-by SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; COUNT(DISTINCT b) 8 8 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 10 NULL 1000 Using index; Using filesort +1 SIMPLE t1 index NULL a 10 NULL 1000 Using index; Using filesort SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b; COUNT(DISTINCT a) 2 @@ -3170,7 +3195,7 @@ COUNT(DISTINCT a) 2 EXPLAIN SELECT DISTINCT COUNT(DISTINCT a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 10 NULL 1000 Using index +1 SIMPLE t1 index NULL a 10 NULL 1000 Using index SELECT DISTINCT COUNT(DISTINCT a) FROM t1; COUNT(DISTINCT a) 2 @@ -3188,76 +3213,76 @@ COUNT(DISTINCT a) 2 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 NULL +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; COUNT(DISTINCT a) 2 EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; 1 1 EXPLAIN SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 10 NULL 1001 Using index for group-by (scanning) +1 SIMPLE t1 range NULL a 10 NULL 501 Using index for group-by SELECT 1 FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 1; 1 1 1 EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1_1 index a a 10 NULL 1000 Using index; Using temporary; Using filesort -1 SIMPLE t1_2 index NULL a 10 NULL 1000 Using index; Using join buffer (Block Nested Loop) +1 SIMPLE t1_1 index NULL a 10 NULL 1000 Using index; Using temporary; Using filesort +1 SIMPLE t1_2 index NULL a 10 NULL 1000 Using index; Using join buffer (flat, BNL join) SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a; COUNT(DISTINCT t1_1.a) 1 1 EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 501 Using index for group-by +1 SIMPLE t1 range NULL a 5 NULL 251 Using index for group-by SELECT COUNT(DISTINCT a), 12 FROM t1; COUNT(DISTINCT a) 12 2 12 EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 15 NULL 1001 Using index for group-by (scanning) +1 SIMPLE t2 range NULL a 15 NULL 501 Using index for group-by SELECT COUNT(DISTINCT a, b, c) FROM t2; COUNT(DISTINCT a, b, c) 16 EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 251 Using index for group-by +1 SIMPLE t2 range NULL a 5 NULL 126 Using index for group-by SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT a) 2 3 1.5000 EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 NULL +1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT f) FROM t2; COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT f) 2 3 1.0000 EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 10 NULL 501 Using index for group-by (scanning) +1 SIMPLE t2 range NULL a 10 NULL 251 Using index for group-by SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; COUNT(DISTINCT a, b) COUNT(DISTINCT b, a) 16 16 EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 NULL +1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, f) FROM t2; COUNT(DISTINCT a, b) COUNT(DISTINCT b, f) 16 8 EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 NULL +1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, d) FROM t2; COUNT(DISTINCT a, b) COUNT(DISTINCT b, d) 16 8 EXPLAIN SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 15 NULL 1001 Using index for group-by (scanning) +1 SIMPLE t2 range NULL a 15 NULL 501 Using index for group-by SELECT a, c, COUNT(DISTINCT c, a, b) FROM t2 GROUP BY a, b, c; a c COUNT(DISTINCT c, a, b) 1 1 1 @@ -3279,7 +3304,7 @@ a c COUNT(DISTINCT c, a, b) EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2 WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 15 NULL 1001 Using where; Using index for group-by (scanning) +1 SIMPLE t2 range a a 15 NULL 501 Using where; Using index for group-by SELECT COUNT(DISTINCT c, a, b) FROM t2 WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; COUNT(DISTINCT c, a, b) @@ -3292,28 +3317,28 @@ GROUP BY b; COUNT(DISTINCT b) SUM(DISTINCT b) EXPLAIN SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 10 NULL 501 Using index for group-by (scanning) +1 SIMPLE t2 range NULL a 10 NULL 251 Using index for group-by SELECT a, COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; a COUNT(DISTINCT b) SUM(DISTINCT b) 1 8 36 2 8 36 EXPLAIN SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 10 NULL 501 Using index for group-by (scanning) +1 SIMPLE t2 range NULL a 10 NULL 251 Using index for group-by SELECT COUNT(DISTINCT b), SUM(DISTINCT b) FROM t2 GROUP BY a; COUNT(DISTINCT b) SUM(DISTINCT b) 8 36 8 36 EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a NULL NULL NULL 1000 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 Using where SELECT COUNT(DISTINCT a, b) FROM t2 WHERE c = 13 AND d = 42; COUNT(DISTINCT a, b) 0 EXPLAIN SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 WHERE b = 13 AND c = 42 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 15 NULL 251 Using where; Using index for group-by +1 SIMPLE t2 range NULL a 15 NULL 126 Using where; Using index for group-by SELECT a, COUNT(DISTINCT a), SUM(DISTINCT a) FROM t2 WHERE b = 13 AND c = 42 GROUP BY a; a COUNT(DISTINCT a) SUM(DISTINCT a) @@ -3327,14 +3352,14 @@ COUNT(DISTINCT a, b) SUM(DISTINCT a) 0 NULL EXPLAIN SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index a a 15 NULL 1000 Using index +1 SIMPLE t2 index NULL a 15 NULL 1000 Using index SELECT SUM(DISTINCT a), MAX(b) FROM t2 GROUP BY a; SUM(DISTINCT a) MAX(b) 1 8 2 8 EXPLAIN SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 15 NULL 1001 Using index for group-by (scanning) +1 SIMPLE t2 range NULL a 15 NULL 501 Using index for group-by SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; 42 * (a + c + COUNT(DISTINCT c, a, b)) 126 @@ -3355,7 +3380,7 @@ SELECT 42 * (a + c + COUNT(DISTINCT c, a, b)) FROM t2 GROUP BY a, b, c; 168 EXPLAIN SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index a a 15 NULL 1000 Using index +1 SIMPLE t2 index NULL a 15 NULL 1000 Using index SELECT (SUM(DISTINCT a) + MAX(b)) FROM t2 GROUP BY a; (SUM(DISTINCT a) + MAX(b)) 9 @@ -3383,7 +3408,7 @@ f1 COUNT(DISTINCT f2) 3 4 explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 5 NULL 1000 Using index +1 SIMPLE t1 index NULL PRIMARY 5 NULL 1000 Using index drop table t1; # End of test#50539. # @@ -3395,9 +3420,6 @@ INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (1,0), (3,2), (4,5); ANALYZE TABLE t; Table Op Msg_type Msg_text test.t analyze status OK -set optimizer_trace_max_mem_size=1048576; -set @@session.optimizer_trace='enabled=on'; -set end_markers_in_json=on; ANALYZE TABLE t; Table Op Msg_type Msg_text test.t analyze status OK @@ -3409,11 +3431,7 @@ a SUM(DISTINCT a) MIN(b) 4 4 4 EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b) FROM t GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index a a 10 NULL 1000 Using index -SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK -FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; -OK -1 +1 SIMPLE t index NULL a 10 NULL 1000 Using index SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; a SUM(DISTINCT a) MAX(b) 1 1 1 @@ -3422,11 +3440,7 @@ a SUM(DISTINCT a) MAX(b) 4 4 5 EXPLAIN SELECT a, SUM(DISTINCT a), MAX(b) FROM t GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index a a 10 NULL 1000 Using index -SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK -FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; -OK -1 +1 SIMPLE t index NULL a 10 NULL 1000 Using index SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); a MAX(b) 1 1 @@ -3435,21 +3449,13 @@ a MAX(b) 4 5 EXPLAIN SELECT a, MAX(b) FROM t GROUP BY a HAVING SUM(DISTINCT a); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index a a 10 NULL 1000 Using index -SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK -FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; -OK -1 +1 SIMPLE t index NULL a 10 NULL 1000 Using index SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; SUM(DISTINCT a) MIN(b) MAX(b) 10 0 5 EXPLAIN SELECT SUM(DISTINCT a), MIN(b), MAX(b) FROM t; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index a a 10 NULL 1000 Using index -SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK -FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; -OK -1 +1 SIMPLE t index NULL a 10 NULL 1000 Using index SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; a SUM(DISTINCT a) MIN(b) MAX(b) 1 1 0 1 @@ -3458,14 +3464,7 @@ a SUM(DISTINCT a) MIN(b) MAX(b) 4 4 4 5 EXPLAIN SELECT a, SUM(DISTINCT a), MIN(b), MAX(b) FROM t GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t index a a 10 NULL 1000 Using index -SELECT TRACE RLIKE 'have_both_agg_distinct_and_min_max' AS OK -FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; -OK -1 -SET optimizer_trace_max_mem_size=DEFAULT; -SET optimizer_trace=DEFAULT; -SET end_markers_in_json=DEFAULT; +1 SIMPLE t index NULL a 10 NULL 1000 Using index DROP TABLE t; # # Bug#18109609: LOOSE INDEX SCAN IS NOT USED WHEN IT SHOULD @@ -3484,7 +3483,7 @@ Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c1 c1 5 NULL 251 Using where; Using index for group-by +1 SIMPLE t1 ref c1 c1 5 const 1000 Using index FLUSH STATUS; SELECT MAX(c2), c1 FROM t1 WHERE c1 = 4 GROUP BY c1; MAX(c2) c1 @@ -3492,12 +3491,14 @@ MAX(c2) c1 SHOW SESSION STATUS LIKE 'Handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 3 -Handler_read_last 1 -Handler_read_next 0 +Handler_read_key 1 +Handler_read_last 0 +Handler_read_next 20 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 DROP TABLE t1; # End of test for Bug#18109609 -set global debug="-d,force_group_by"; +set global debug_dbug=@debug_tmp; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/group_min_max.test b/storage/rocksdb/mysql-test/rocksdb/t/group_min_max.test index a9c44a71edd..eb66bd0e972 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/group_min_max.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/group_min_max.test @@ -1,8 +1,9 @@ --source include/have_debug.inc -set global debug="+d,force_group_by"; +set @debug_tmp= @@debug_dbug; +set global debug_dbug="+d,force_group_by"; let $engine=RocksDB; --source include/group_min_max.inc -set global debug="-d,force_group_by"; +set global debug_dbug=@debug_tmp;

1 0

[Commits] 1e58c579f5b: MDEV-18930: Failed CREATE OR REPLACE TEMPORARY not written into binary log makes data on master and slave diverge
by sujatha 02 Aug '19

02 Aug '19

revision-id: 1e58c579f5b61644a74045a0470d2c62b343eb75 (mariadb-10.1.41-3-g1e58c579f5b) parent(s): 0bb8f33b55cc016d0ede86b97990a3ee30dcb069 author: Sujatha committer: Sujatha timestamp: 2019-08-01 16:16:05 +0530 message: MDEV-18930: Failed CREATE OR REPLACE TEMPORARY not written into binary log makes data on master and slave diverge Problem: ======= Failed CREATE OR REPLACE TEMPORARY TABLE statement which dropped the table but failed at a later stage of creation of temporary table is not written to binarylog in row based replication. This causes the slave to diverge. Analysis: ======== CREATE OR REPLACE statements work as shown below. CREATE OR REPLACE TABLE table_name (a int); is basically the same as: DROP TABLE IF EXISTS table_name; CREATE TABLE table_name (a int); Hence every CREATE OR REPLACE TABLE command which dropped the table should be written to binary log, even when following CREATE TABLE part fails. In order to achieve this, during the execution of CREATE OR REPLACE command, when a table is dropped 'thd->log_current_statement' flag is set. When table creation results in an error within 'mysql_create_table' code, the error handling part looks for this flag. If it is set the failed CREATE OR REPLACE statement is written into the binary log inspite of error. This ensure that slave doesn't diverge from the master. In case of row based replication the error handling code returns very early, if the table is of type temporary. This is done based on the assumption that temporary tables are not replicated in row based replication. It fails to handle the cases where a temporary table was created as part of statement based replication at an earlier stage and the binary log format was changed to row because of an unsafe statement. In this case when a CREATE OR REPLACE statement is executed on this temporary table it will dropped but the query will not be written to binary log. Hence slave diverges. Fix: === In error handling code check the return status of create table operation. If it is successful and replication mode is row based and table is of type temporary then return. Other wise proceed further to the code which checks for thd->log_current_statement flag and does appropriate logging. --- .../suite/rpl/r/rpl_create_or_replace_fail.result | 18 +++++++ .../suite/rpl/t/rpl_create_or_replace_fail.test | 56 ++++++++++++++++++++++ sql/sql_table.cc | 2 +- 3 files changed, 75 insertions(+), 1 deletion(-) diff --git a/mysql-test/suite/rpl/r/rpl_create_or_replace_fail.result b/mysql-test/suite/rpl/r/rpl_create_or_replace_fail.result new file mode 100644 index 00000000000..57178f0efbe --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_create_or_replace_fail.result @@ -0,0 +1,18 @@ +include/master-slave.inc +[connection master] +CREATE TEMPORARY TABLE t1 (a INT NOT NULL); +LOAD DATA INFILE 'x' INTO TABLE x; +ERROR 42S02: Table 'test.x' doesn't exist +CREATE OR REPLACE TEMPORARY TABLE t1 (x INT) PARTITION BY HASH(x); +ERROR HY000: Cannot create temporary table with partitions +"************** DROP TEMPORARY TABLE Should be present in Binary log **************" +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t1 (a INT NOT NULL) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TEMPORARY TABLE t1 (x INT) PARTITION BY HASH(x) +CREATE TABLE t1 (b INT); +INSERT INTO t1 VALUES (NULL); +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_create_or_replace_fail.test b/mysql-test/suite/rpl/t/rpl_create_or_replace_fail.test new file mode 100644 index 00000000000..e75f34b0b56 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_create_or_replace_fail.test @@ -0,0 +1,56 @@ +# ==== Purpose ==== +# +# Test verifies that failed CREATE OR REPLACE TEMPORARY TABLE statement which +# dropped the table but failed at a later stage of creation of temporary table +# is written to binarylog in row based replication. +# +# ==== Implementation ==== +# +# Steps: +# 0 - Have mixed based replication mode. +# 1 - Create a temporary table. It will be replicated as mixed replication +# mode is in use. +# 2 - Execute an unsafe statement which will switch current statement +# binlog format to 'ROW'. i.e If binlog_format=MIXED, there are open +# temporary tables, and an unsafe statement is executed, then subsequent +# statements are logged in row format. +# 3 - Execute a CREATE OR REPLACE TEMPORARY TABLE statement which tries to +# create partitions on temporary table. Since it is not supported it will +# fail. +# 4 - Check the binary log output to ensure that the failed statement is +# written to the binary log. +# 5 - Slave should be up and running and in sync with master. +# +# ==== References ==== +# +# MDEV-18930: Failed CREATE OR REPLACE TEMPORARY not written into binary log +# makes data on master and slave diverge +# + +--source include/have_partition.inc +--source include/have_binlog_format_mixed.inc +--source include/master-slave.inc + +CREATE TEMPORARY TABLE t1 (a INT NOT NULL); + +# Execute an unsafe statement which switches replication mode internally from +# "STATEMENT" to "ROW". +--error ER_NO_SUCH_TABLE +LOAD DATA INFILE 'x' INTO TABLE x; + +--error ER_PARTITION_NO_TEMPORARY +CREATE OR REPLACE TEMPORARY TABLE t1 (x INT) PARTITION BY HASH(x); + +--echo "************** DROP TEMPORARY TABLE Should be present in Binary log **************" +--source include/show_binlog_events.inc + +CREATE TABLE t1 (b INT); +INSERT INTO t1 VALUES (NULL); +--sync_slave_with_master + +# Cleanup +--connection master +DROP TABLE t1; + +--source include/rpl_end.inc + diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 656834c7852..fe923d73200 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -5092,7 +5092,7 @@ bool mysql_create_table(THD *thd, TABLE_LIST *create_table, err: /* In RBR we don't need to log CREATE TEMPORARY TABLE */ - if (thd->is_current_stmt_binlog_format_row() && create_info->tmp_table()) + if (!result && thd->is_current_stmt_binlog_format_row() && create_info->tmp_table()) DBUG_RETURN(result); if (create_info->tmp_table())

2 2

[Commits] 0dd2d7d91d2: MDEV-20224: main.derived crashes with ASAN with error use-after-poison
by Varun 01 Aug '19

01 Aug '19

revision-id: 0dd2d7d91d23bcfc035de8a024af40dc36f543ff (mariadb-10.4.4-258-g0dd2d7d91d2) parent(s): 46553c250808f0cc21e91f7e13439add2903aea0 author: Varun Gupta committer: Varun Gupta timestamp: 2019-08-01 11:16:06 +0530 message: MDEV-20224: main.derived crashes with ASAN with error use-after-poison Make sure that the references in the GROUP BY clause that need to be considered for pushdown from having to where are of Item_field objects --- sql/sql_lex.cc | 1 + 1 file changed, 1 insertion(+) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index feda4cd23fb..eceef9a7097 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -7893,6 +7893,7 @@ bool st_select_lex::collect_grouping_fields(THD *thd) Item *item= *ord->item; if (item->type() != Item::FIELD_ITEM && !(item->type() == Item::REF_ITEM && + ((Item_ref*)item)->real_type() == Item::FIELD_ITEM && ((((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF) || (((Item_ref *) item)->ref_type() == Item_ref::REF)))) continue;

1 0

[Commits] c776ed4d6ec: MDEV-19848 Server crashes in check_vcol_forward_refs upon INSERT DELAYED into table with long blob key
by sachin.setiya＠mariadb.com 29 Jul '19

29 Jul '19

revision-id: c776ed4d6ecf9a0393a8eb53cd6660ff3eb63758 (mariadb-10.4.5-153-gc776ed4d6ec) parent(s): 4ca016237f1a1813c9e1ce9e4227c056c53896bb author: Sachin committer: Sachin timestamp: 2019-07-30 03:42:21 +0530 message: MDEV-19848 Server crashes in check_vcol_forward_refs upon INSERT DELAYED into table with long blob key There are 2 issues 1st:- in make_new_field when we & into new field flag we forget LONG_UNIQUE_HASH_FIELD Flag. 2nd:- We are calling parse_vcol_defs on keyinfo , but they are not in right form. We should call setup_keyinfo_hash_all before calling parse_vcol_defs --- mysql-test/main/long_unique_bugs.result | 3 +++ mysql-test/main/long_unique_bugs.test | 8 ++++++++ sql/field.cc | 2 +- sql/sql_insert.cc | 1 + sql/table.cc | 20 ++++++++++++++++++++ sql/table.h | 2 ++ 6 files changed, 35 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 910e5e592e1..4e831bcff92 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -268,3 +268,6 @@ DROP TABLE t1, t2; create table t1(a int, unique(a) using hash); #BULK insert > 100 rows (MI_MIN_ROWS_TO_DISABLE_INDEXES) drop table t1; +CREATE TABLE t1 (a BLOB, UNIQUE(a)) ENGINE=MyISAM; +INSERT DELAYED t1 () VALUES (); +DROP TABLE t1; diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index da054e59f34..b9b10b3d6cd 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -334,3 +334,11 @@ while ($count) --eval $insert_stmt --enable_query_log drop table t1; + +# +# MDEV-19848 Server crashes in check_vcol_forward_refs upon INSERT DELAYED into table with long blob key +# +CREATE TABLE t1 (a BLOB, UNIQUE(a)) ENGINE=MyISAM; +INSERT DELAYED t1 () VALUES (); +# Cleanup +DROP TABLE t1; diff --git a/sql/field.cc b/sql/field.cc index 621f0136bea..caae3bca430 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -2326,7 +2326,7 @@ Field *Field::make_new_field(MEM_ROOT *root, TABLE *new_table, tmp->flags&= (NOT_NULL_FLAG | BLOB_FLAG | UNSIGNED_FLAG | ZEROFILL_FLAG | BINARY_FLAG | ENUM_FLAG | SET_FLAG | VERS_SYS_START_FLAG | VERS_SYS_END_FLAG | - VERS_UPDATE_UNVERSIONED_FLAG); + VERS_UPDATE_UNVERSIONED_FLAG | LONG_UNIQUE_HASH_FIELD); tmp->reset_fields(); tmp->invisible= VISIBLE; return tmp; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index f3a548d7265..a05bc934553 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2623,6 +2623,7 @@ TABLE *Delayed_insert::get_local_table(THD* client_thd) if (share->virtual_fields || share->default_expressions || share->default_fields) { + table->setup_keyinfo_hash_all(); bool error_reported= FALSE; if (unlikely(parse_vcol_defs(client_thd, client_thd->mem_root, copy, &error_reported))) diff --git a/sql/table.cc b/sql/table.cc index 48421c4051e..d859152f700 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9039,6 +9039,26 @@ void re_setup_keyinfo_hash(KEY *key_info) key_info->ext_key_parts= 1; key_info->flags&= ~HA_NOSAME; } + +/* + call setup_keyinfo_hash for all keys in table + */ +void TABLE::setup_keyinfo_hash_all() +{ + for (uint i= 0; i < s->keys; i++) + if (key_info[i].algorithm == HA_KEY_ALG_LONG_HASH) + setup_keyinfo_hash(&key_info[i]); +} + +/* + call re_setup_keyinfo_hash for all keys in table + */ +void TABLE::re_setup_keyinfo_hash_all() +{ + for (uint i= 0; i < s->keys; i++) + if (key_info[i].algorithm == HA_KEY_ALG_LONG_HASH) + re_setup_keyinfo_hash(&key_info[i]); +} /** @brief clone of current handler. Creates a clone of handler used in update for diff --git a/sql/table.h b/sql/table.h index 42c017d63af..40ff7cdba29 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1596,6 +1596,8 @@ struct TABLE void vers_update_fields(); void vers_update_end(); void find_constraint_correlated_indexes(); + void setup_keyinfo_hash_all(); + void re_setup_keyinfo_hash_all(); void clone_handler_for_update(); void delete_update_handler();

1 0