- commits - lists.mariadb.org

[Commits] 24523cb02c5: Test change deleting restart parameters.
by jan 08 Oct '18

08 Oct '18

revision-id: 24523cb02c5ade85fb2f20aeeb6891b0397fe585 (mariadb-10.1.35-83-g24523cb02c5) parent(s): cd9fe4b9586cd7fbc80394e48d93c5b5785ad80a author: Jan Lindström committer: Jan Lindström timestamp: 2018-10-08 18:53:00 +0300 message: Test change deleting restart parameters. --- mysql-test/mysql-test-run.pl | 3 +++ 1 file changed, 3 insertions(+) diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index d969d7bf9f6..3fa707c649e 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -5282,6 +5282,9 @@ sub server_need_restart { exists $server->{'restart_opts'}) { my $use_dynamic_option_switch= 0; + + delete $server->{'restart_opts'}; + if (!$use_dynamic_option_switch) { mtr_verbose_restart($server, "running with different options '" .

1 0

[Commits] 8595361: MDEV-17381 Wrong query result with LATERAL DERIVED optimization
by IgorBabaev 08 Oct '18

08 Oct '18

revision-id: 859536176897305f2e2f089eeba77871aefdb79c (mariadb-10.3.10-10-g8595361) parent(s): e2535dcc04b5ecc15575b878ebeb0cc589cd23fe author: Igor Babaev committer: Igor Babaev timestamp: 2018-10-08 06:55:48 -0700 message: MDEV-17381 Wrong query result with LATERAL DERIVED optimization and join_cache_level=6 This bug was fixed by the patch for mdev-17382 applied to 5.5. --- mysql-test/main/derived_split_innodb.result | 41 +++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 41 +++++++++++++++++++++++++++++ 2 files changed, 82 insertions(+) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 7e4ba8e..21dbd49 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -58,3 +58,44 @@ WHERE t2.id2=t.id2; id3 1 DROP TABLE t1,t2,t3; +# +# Bug mdev-17381: equi-join of derived table with join_cache_level=4 +# +CREATE TABLE t1 ( +id int NOT NULL, +amount decimal DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=INNODB; +CREATE TABLE t2 ( +id int NOT NULL, +name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=INNODB; +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); +set join_cache_level=4; +SELECT t2.id,t2.name,t.total_amt +FROM t2 +LEFT JOIN +(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t +ON t2.id=t.id +WHERE t2.id < 3; +id name total_amt +1 A 10 +2 B 20 +EXPLAIN SELECT t2.id,t2.name,t.total_amt +FROM t2 +LEFT JOIN +(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t +ON t2.id=t.id +WHERE t2.id < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +2 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 +set join_cache_level=default; +DROP TABLE t1,t2; diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 5e5e3d5..c3b3bca 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -53,3 +53,44 @@ eval EXPLAIN $q; eval $q; DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug mdev-17381: equi-join of derived table with join_cache_level=4 +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + amount decimal DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=INNODB; + +CREATE TABLE t2 ( + id int NOT NULL, + name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=INNODB; + +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); + +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); + +set join_cache_level=4; + +let $q= +SELECT t2.id,t2.name,t.total_amt + FROM t2 + LEFT JOIN + (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t + ON t2.id=t.id + WHERE t2.id < 3; + +eval $q; +eval EXPLAIN $q; + +set join_cache_level=default; + +DROP TABLE t1,t2;

1 0

[Commits] 9fa03e52953: MDEV-17098 DATE -> DATETIME replication conversion not working, even in ALL_NON_LOSSY mode
by andrei.elkin＠pp.inet.fi 08 Oct '18

08 Oct '18

revision-id: 9fa03e5295366f66499037e7aaeab342c9ea3887 (mariadb-10.2.18-8-g9fa03e52953) parent(s): 753117fed043ac3200093c2dc28161ebce2cd04b author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-10-08 16:45:49 +0300 message: MDEV-17098 DATE -> DATETIME replication conversion not working, even in ALL_NON_LOSSY mode Opened up MYSQL_TYPE _DATETIME{,2} <-> _NEWDATE conversions for replication. --- mysql-test/suite/rpl/include/rpl_colsize_check.inc | 23 +++ mysql-test/suite/rpl/r/rpl_colSize.result | 196 +++++++++++++++++++++ mysql-test/suite/rpl/r/rpl_row_colSize.result | 24 +++ mysql-test/suite/rpl/t/rpl_colSize.test | 130 ++++++++++++++ mysql-test/suite/rpl/t/rpl_row_colSize.test | 10 ++ sql/rpl_utility.cc | 34 +++- 6 files changed, 415 insertions(+), 2 deletions(-) diff --git a/mysql-test/suite/rpl/include/rpl_colsize_check.inc b/mysql-test/suite/rpl/include/rpl_colsize_check.inc new file mode 100644 index 00000000000..0c5bdd56610 --- /dev/null +++ b/mysql-test/suite/rpl/include/rpl_colsize_check.inc @@ -0,0 +1,23 @@ +# Create a table on master and slave possibly with imcompatible attributes; +# INSERT into the table and replicate; +# print out inserted values. +--connection master +--eval DROP TABLE IF EXISTS $table; +sync_slave_with_master slave; + +--connection master +--eval CREATE TABLE $table ($attr_m) +--replace_result default DEFAULT +--eval SHOW CREATE TABLE $table + +sync_slave_with_master slave; +--eval ALTER TABLE $table MODIFY $attr_s +--replace_result default DEFAULT +--eval SHOW CREATE TABLE $table + +--connection master +--eval INSERT INTO t1 VALUES $values2 +--eval SELECT $check_m from $table + +sync_slave_with_master slave; +--eval SELECT $check_s from $table diff --git a/mysql-test/suite/rpl/r/rpl_colSize.result b/mysql-test/suite/rpl/r/rpl_colSize.result index 503bf85222e..96bbbb61f93 100644 --- a/mysql-test/suite/rpl/r/rpl_colSize.result +++ b/mysql-test/suite/rpl/r/rpl_colSize.result @@ -189,6 +189,202 @@ t1 CREATE TABLE `t1` ( `d` bit(25) DEFAULT NULL, `e` bit(13) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection master; +SET @saved_mysql56_temporal_format = @@global.mysql56_temporal_format; +connection slave; +SET @saved_mysql56_temporal_format = @@global.mysql56_temporal_format; +MYSQL_TYPE_NEWDATE <-> MYSQL_TYPE_DATETIME2 +connection master; +SET @@global.mysql56_temporal_format=1; +connection slave; +SET @@global.mysql56_temporal_format=1; +Checking NON-LOSSY MYSQL_TYPE_NEWDATE -> MYSQL_TYPE_DATETIME2 +connection master; +DROP TABLE IF EXISTS t1;; +connection slave; +connection master; +CREATE TABLE t1 (a DATE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection slave; +ALTER TABLE t1 MODIFY a DATETIME(6); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(6) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection master; +INSERT INTO t1 VALUES (current_date()),(date_add(current_date(), INTERVAL 1 day)); +SELECT max(a) - min(a) as 'master: one' from t1; +master: one +1 +connection slave; +SELECT date(max(a)) - date(min(a)) as 'slave: 1' from t1; +slave: 1 +1 +Checking LOSSY MYSQL_TYPE_DATETIME2 -> MYSQL_TYPE_NEWDATE +SET @local_saved_slave_type_conversions = @@slave_type_conversions; +SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY'; +connection master; +DROP TABLE IF EXISTS t1;; +connection slave; +connection master; +CREATE TABLE t1 (a DATETIME(6)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(6) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection slave; +ALTER TABLE t1 MODIFY a DATE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection master; +INSERT INTO t1 VALUES (current_timestamp(6)), (date_add(current_timestamp(6), INTERVAL 1 day)); +SELECT date(max(a)) - date(min(a)) as 'master: one day' from t1; +master: one day +1 +connection slave; +SELECT max(a) - min(a) as 'slave: 1 day' from t1; +slave: 1 day +1 +SET GLOBAL SLAVE_TYPE_CONVERSIONS = @local_saved_slave_type_conversions; +"Mysql" MYSQL_TYPE_NEWDATE <-> MYSQL_TYPE_DATETIME +connection master; +SET @@global.mysql56_temporal_format=1; +connection slave; +SET @@global.mysql56_temporal_format=0; +Checking NON-LOSSY MYSQL_TYPE_NEWDATE -> MYSQL_TYPE_DATETIME +connection master; +DROP TABLE IF EXISTS t1;; +connection slave; +connection master; +CREATE TABLE t1 (a DATE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection slave; +ALTER TABLE t1 MODIFY a DATETIME(6); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(6) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection master; +INSERT INTO t1 VALUES (current_date()),(date_add(current_date(), INTERVAL 1 day)); +SELECT max(a) - min(a) as 'master: one' from t1; +master: one +1 +connection slave; +SELECT date(max(a)) - date(min(a)) as 'slave: 1' from t1; +slave: 1 +1 +Checking LOSSY MYSQL_TYPE_DATETIME2 -> MYSQL_TYPE_DATE +SET @local_saved_slave_type_conversions = @@slave_type_conversions; +SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY'; +connection master; +DROP TABLE IF EXISTS t1;; +connection slave; +connection master; +CREATE TABLE t1 (a DATETIME(6)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(6) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection slave; +ALTER TABLE t1 MODIFY a DATE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection master; +INSERT INTO t1 VALUES (current_timestamp(6)), (date_add(current_timestamp(6), INTERVAL 1 day)); +SELECT date(max(a)) - date(min(a)) as 'master: one day' from t1; +master: one day +1 +connection slave; +SELECT max(a) - min(a) as 'slave: 1 day' from t1; +slave: 1 day +1 +SET GLOBAL SLAVE_TYPE_CONVERSIONS = @local_saved_slave_type_conversions; +"Maria" MYSQL_TYPE_NEWDATE <-> MYSQL_TYPE_DATETIME2 +connection master; +SET @@global.mysql56_temporal_format=0; +connection slave; +SET @@global.mysql56_temporal_format=1; +Checking NON-LOSSY MYSQL_TYPE_NEWDATE -> MYSQL_TYPE_DATETIME +connection master; +DROP TABLE IF EXISTS t1;; +connection slave; +connection master; +CREATE TABLE t1 (a DATE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection slave; +ALTER TABLE t1 MODIFY a DATETIME(6); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(6) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection master; +INSERT INTO t1 VALUES (current_date()),(date_add(current_date(), INTERVAL 1 day)); +SELECT max(a) - min(a) as 'master: one' from t1; +master: one +1 +connection slave; +SELECT date(max(a)) - date(min(a)) as 'slave: 1' from t1; +slave: 1 +1 +"Maria" MYSQL_TYPE_NEWDATE <-> MYSQL_TYPE_DATETIME +connection master; +SET @@global.mysql56_temporal_format=0; +connection slave; +SET @@global.mysql56_temporal_format=0; +Checking NON-LOSSY MYSQL_TYPE_NEWDATE -> MYSQL_TYPE_DATETIME +connection master; +DROP TABLE IF EXISTS t1;; +connection slave; +connection master; +CREATE TABLE t1 (a DATE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection slave; +ALTER TABLE t1 MODIFY a DATETIME(6); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(6) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection master; +INSERT INTO t1 VALUES (current_date()),(date_add(current_date(), INTERVAL 1 day)); +SELECT max(a) - min(a) as 'master: one' from t1; +master: one +1 +connection slave; +SELECT date(max(a)) - date(min(a)) as 'slave: 1' from t1; +slave: 1 +1 +connection master; +SET @@global.mysql56_temporal_format=@saved_mysql56_temporal_format; +connection slave; +SET @@global.mysql56_temporal_format=@saved_mysql56_temporal_format; *** Cleanup *** connection master; DROP TABLE t1; diff --git a/mysql-test/suite/rpl/r/rpl_row_colSize.result b/mysql-test/suite/rpl/r/rpl_row_colSize.result index dd324ef7807..ede73cbeedd 100644 --- a/mysql-test/suite/rpl/r/rpl_row_colSize.result +++ b/mysql-test/suite/rpl/r/rpl_row_colSize.result @@ -344,6 +344,30 @@ connection master; RESET MASTER; connection slave; START SLAVE; +Checking MYSQL_TYPE_DATETIME to MYSQL_TYPE_DATE +connection master; +DROP TABLE IF EXISTS t1; +connection slave; +STOP SLAVE; +RESET SLAVE; +CREATE TABLE t1 (a date); +connection master; +CREATE TABLE t1 (a datetime(6)); +RESET MASTER; +INSERT INTO t1 VALUES (current_date()); +connection slave; +START SLAVE; +include/wait_for_slave_sql_error.inc [errno=1677] +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'datetime' to type 'date'' +SELECT COUNT(*) FROM t1; +COUNT(*) +0 +STOP SLAVE; +RESET SLAVE; +connection master; +RESET MASTER; +connection slave; +START SLAVE; connection slave; call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column 0 ...e mismatch.* error.* 1535"); call mtr.add_suppression("Slave SQL.*Column 0 of table .test.t1. cannot be converted from type.* error.* 1677"); diff --git a/mysql-test/suite/rpl/t/rpl_colSize.test b/mysql-test/suite/rpl/t/rpl_colSize.test index d6f817af189..abf230dd1b5 100644 --- a/mysql-test/suite/rpl/t/rpl_colSize.test +++ b/mysql-test/suite/rpl/t/rpl_colSize.test @@ -217,6 +217,136 @@ SELECT BIN(a), BIN(b), BIN(c), BIN(d), BIN(e) FROM t1; --replace_result default DEFAULT SHOW CREATE TABLE t1; +# +# MDEV-17098: Checking MYSQL_TYPE _DATETIME{,2} to-from _{NEW,}DATE conversion +# + +connection master; +SET @saved_mysql56_temporal_format = @@global.mysql56_temporal_format; +connection slave; +SET @saved_mysql56_temporal_format = @@global.mysql56_temporal_format; + +--echo MYSQL_TYPE_NEWDATE <-> MYSQL_TYPE_DATETIME2 +connection master; +SET @@global.mysql56_temporal_format=1; +connection slave; +SET @@global.mysql56_temporal_format=1; +--echo Checking NON-LOSSY MYSQL_TYPE_NEWDATE -> MYSQL_TYPE_DATETIME2 +--let $table = t1 +--let $attr_m = a DATE +--let $attr_s = a DATETIME(6) +--let $values2 = (current_date()),(date_add(current_date(), INTERVAL 1 day)) +--let $check_m = max(a) - min(a) as 'master: one' +--let $check_s = date(max(a)) - date(min(a)) as 'slave: 1' +--source include/rpl_colsize_check.inc + +--echo Checking LOSSY MYSQL_TYPE_DATETIME2 -> MYSQL_TYPE_NEWDATE +SET @local_saved_slave_type_conversions = @@slave_type_conversions; +SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY'; +--let $attr_m = a DATETIME(6) +--let $attr_s = a DATE +--let $values2 = (current_timestamp(6)), (date_add(current_timestamp(6), INTERVAL 1 day)) +--let $check_m = date(max(a)) - date(min(a)) as 'master: one day' +--let $check_s = max(a) - min(a) as 'slave: 1 day' +--source include/rpl_colsize_check.inc +SET GLOBAL SLAVE_TYPE_CONVERSIONS = @local_saved_slave_type_conversions; + +--echo "Mysql" MYSQL_TYPE_NEWDATE <-> MYSQL_TYPE_DATETIME +connection master; +SET @@global.mysql56_temporal_format=1; +connection slave; +SET @@global.mysql56_temporal_format=0; + +--echo Checking NON-LOSSY MYSQL_TYPE_NEWDATE -> MYSQL_TYPE_DATETIME +--let $table = t1 +--let $attr_m = a DATE +--let $attr_s = a DATETIME(6) +--let $values2 = (current_date()),(date_add(current_date(), INTERVAL 1 day)) +--let $check_m = max(a) - min(a) as 'master: one' +--let $check_s = date(max(a)) - date(min(a)) as 'slave: 1' +--source include/rpl_colsize_check.inc + +--echo Checking LOSSY MYSQL_TYPE_DATETIME2 -> MYSQL_TYPE_DATE +SET @local_saved_slave_type_conversions = @@slave_type_conversions; +SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY'; +--let $attr_m = a DATETIME(6) +--let $attr_s = a DATE +--let $values2 = (current_timestamp(6)), (date_add(current_timestamp(6), INTERVAL 1 day)) +--let $check_m = date(max(a)) - date(min(a)) as 'master: one day' +--let $check_s = max(a) - min(a) as 'slave: 1 day' +--source include/rpl_colsize_check.inc +SET GLOBAL SLAVE_TYPE_CONVERSIONS = @local_saved_slave_type_conversions; + +--echo "Maria" MYSQL_TYPE_NEWDATE <-> MYSQL_TYPE_DATETIME2 +connection master; +SET @@global.mysql56_temporal_format=0; +connection slave; +SET @@global.mysql56_temporal_format=1; + +--echo Checking NON-LOSSY MYSQL_TYPE_NEWDATE -> MYSQL_TYPE_DATETIME +--let $table = t1 +--let $attr_m = a DATE +--let $attr_s = a DATETIME(6) +--let $values2 = (current_date()),(date_add(current_date(), INTERVAL 1 day)) +--let $check_m = max(a) - min(a) as 'master: one' +--let $check_s = date(max(a)) - date(min(a)) as 'slave: 1' +--source include/rpl_colsize_check.inc + +# +# TODO: fix MDEV-17394 Row-based replication DATETIME(m) to +# DATETIME(s) does not work or incorrect +# +#--echo Checking LOSSY MYSQL_TYPE_DATETIME -> MYSQL_TYPE_NEWDATE +#SET @local_saved_slave_type_conversions = @@slave_type_conversions; +#SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY'; +#--let $attr_m = a DATETIME(6) +#--let $attr_s = a DATETIME(0) +#--let $values2 = (current_timestamp(6)), (date_add(current_timestamp(6), INTERVAL 1 day)) +#--let $check_m = date(max(a)) - date(min(a)) as 'master: one day' +#--let $check_s = max(a) - min(a) as 'slave: 1 day' + +### TODO: find out why +# AddressSanitizer: global-buffer-overflow on address 0x55b1fa26aceb at pc 0x55b1f8d46d5c bp 0x7f513f2aa530 sp 0x7f513f2aa520 +#READ of size 1 at 0x55b1fa26aceb thread T35 +# #0 0x55b1f8d46d5b in check_date /home/andrei/MDB/WTs/10.2/10.2-MDEV-17098/sql-common/my_time.c:91 +# #1 0x55b1f7b7b41e in Field_temporal_with_date::store_time_dec(st_mysql_time*, unsigned int) /home/andrei/MDB/WTs/10.2/10.2-MDEV-17098/sql/field.cc:5675 +# #2 0x55b1f7bb9f6a in Field::do_field_temporal(Copy_field*) /home/andrei/MDB/WTs/10.2/10.2-MDEV-17098/sql/field_conv.cc:434 +#--source include/rpl_colsize_check.inc +#SET GLOBAL SLAVE_TYPE_CONVERSIONS = @local_saved_slave_type_conversions; + +--echo "Maria" MYSQL_TYPE_NEWDATE <-> MYSQL_TYPE_DATETIME +connection master; +SET @@global.mysql56_temporal_format=0; +connection slave; +SET @@global.mysql56_temporal_format=0; + +--echo Checking NON-LOSSY MYSQL_TYPE_NEWDATE -> MYSQL_TYPE_DATETIME +--let $table = t1 +--let $attr_m = a DATE +--let $attr_s = a DATETIME(6) +--let $values2 = (current_date()),(date_add(current_date(), INTERVAL 1 day)) +--let $check_m = max(a) - min(a) as 'master: one' +--let $check_s = date(max(a)) - date(min(a)) as 'slave: 1' +--source include/rpl_colsize_check.inc + +#--echo Checking LOSSY MYSQL_TYPE_DATETIME -> MYSQL_TYPE_NEWDATE +#SET @local_saved_slave_type_conversions = @@slave_type_conversions; +#SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY'; +#--let $attr_m = a DATETIME(6) +#--let $attr_s = a DATE +#--let $values2 = (current_timestamp(6)), (date_add(current_timestamp(6), INTERVAL 1 day)) +#--let $check_m = date(max(a)) - date(min(a)) as 'master: one day' +#--let $check_s = max(a) - min(a) as 'slave: 1 day' + +#### The same as above +####--source include/rpl_colsize_check.inc +#SET GLOBAL SLAVE_TYPE_CONVERSIONS = @local_saved_slave_type_conversions; + +connection master; +SET @@global.mysql56_temporal_format=@saved_mysql56_temporal_format; +connection slave; +SET @@global.mysql56_temporal_format=@saved_mysql56_temporal_format; + --echo *** Cleanup *** connection master; DROP TABLE t1; diff --git a/mysql-test/suite/rpl/t/rpl_row_colSize.test b/mysql-test/suite/rpl/t/rpl_row_colSize.test index 21c68b55e92..63659c07a0a 100644 --- a/mysql-test/suite/rpl/t/rpl_row_colSize.test +++ b/mysql-test/suite/rpl/t/rpl_row_colSize.test @@ -160,6 +160,16 @@ let $test_table_slave = CREATE TABLE t1 (a TINYBLOB); let $test_insert = INSERT INTO t1 VALUES ('This is a test.'); source include/test_fieldsize.inc; +# +# Test: Checking MYSQL_TYPE_DATE* fields +# +--echo Checking MYSQL_TYPE_DATETIME to MYSQL_TYPE_DATE +let $test_table_master = CREATE TABLE t1 (a datetime(6)); +let $test_table_slave = CREATE TABLE t1 (a date); +let $test_insert = INSERT INTO t1 VALUES (current_date()); +source include/test_fieldsize.inc; + + connection slave; call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column 0 ...e mismatch.* error.* 1535"); call mtr.add_suppression("Slave SQL.*Column 0 of table .test.t1. cannot be converted from type.* error.* 1677"); diff --git a/sql/rpl_utility.cc b/sql/rpl_utility.cc index bdf5b7dea80..9554daeedbd 100644 --- a/sql/rpl_utility.cc +++ b/sql/rpl_utility.cc @@ -765,14 +765,44 @@ can_convert_field_to(Field *field, case MYSQL_TYPE_TIME: case MYSQL_TYPE_DATETIME: case MYSQL_TYPE_YEAR: - case MYSQL_TYPE_NEWDATE: case MYSQL_TYPE_NULL: case MYSQL_TYPE_ENUM: case MYSQL_TYPE_SET: case MYSQL_TYPE_TIMESTAMP2: - case MYSQL_TYPE_DATETIME2: case MYSQL_TYPE_TIME2: DBUG_RETURN(false); + case MYSQL_TYPE_NEWDATE: + { + if (field->real_type() == MYSQL_TYPE_DATETIME2 || + field->real_type() == MYSQL_TYPE_DATETIME) + { + *order_var= -1; + DBUG_RETURN(is_conversion_ok(*order_var, rli)); + } + else + { + DBUG_RETURN(false); + } + } + break; + + //case MYSQL_TYPE_DATETIME: TODO: fix MDEV-17394 and uncomment. + // + //The "old" type does not specify the fraction part size which is required + //for correct conversion. + case MYSQL_TYPE_DATETIME2: + { + if (field->real_type() == MYSQL_TYPE_NEWDATE) + { + *order_var= 1; + DBUG_RETURN(is_conversion_ok(*order_var, rli)); + } + else + { + DBUG_RETURN(false); + } + } + break; } DBUG_RETURN(false); // To keep GCC happy }

1 0

[Commits] e2535dc: MDEV-17382 Hash join algorithm should not be used to join materialized
by IgorBabaev 08 Oct '18

08 Oct '18

revision-id: e2535dcc04b5ecc15575b878ebeb0cc589cd23fe (mariadb-10.3.10-9-ge2535dc) parent(s): fbee31418cf08c62d91195b04a9ed6a42c31bcec author: Igor Babaev committer: Igor Babaev timestamp: 2018-10-08 06:19:27 -0700 message: MDEV-17382 Hash join algorithm should not be used to join materialized derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table. Change for the test case in 10.3: splitting must be turned off to preserve the explain. --- mysql-test/main/derived_opt.result | 38 ++++++++++++++++++++++++++++++- mysql-test/main/derived_opt.test | 43 +++++++++++++++++++++++++++++++++++ mysql-test/main/innodb_mrr_cpk.result | 2 +- sql/sql_select.cc | 8 +++++++ 4 files changed, 89 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/derived_opt.result b/mysql-test/main/derived_opt.result index 6e4ea1b..48ac7e6 100644 --- a/mysql-test/main/derived_opt.result +++ b/mysql-test/main/derived_opt.result @@ -499,9 +499,45 @@ where D1.a= t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 5 test.t1.a 100 Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 2 DERIVED t2 ALL NULL NULL NULL NULL 100 Using filesort set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +# +# Bug mdev-17382: equi-join of derived table with join_cache_level=4 +# +CREATE TABLE t1 ( +id int NOT NULL, +amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='split_materialized=off'; +set join_cache_level=4; +EXPLAIN +SELECT t2.id,t2.name,t.total_amt +FROM t2 +LEFT JOIN +(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t +ON t2.id=t.id +WHERE t2.id < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 3 Using index condition +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +set join_cache_level=default; +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2; set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/main/derived_opt.test b/mysql-test/main/derived_opt.test index 7f19553..eccf4c1 100644 --- a/mysql-test/main/derived_opt.test +++ b/mysql-test/main/derived_opt.test @@ -363,5 +363,48 @@ set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +--echo # +--echo # Bug mdev-17382: equi-join of derived table with join_cache_level=4 +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); + +CREATE TABLE t2 ( + id int NOT NULL, + name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); + +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); + +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); + +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='split_materialized=off'; + +set join_cache_level=4; + +EXPLAIN +SELECT t2.id,t2.name,t.total_amt + FROM t2 + LEFT JOIN + (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t + ON t2.id=t.id + WHERE t2.id < 3; + +set join_cache_level=default; + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1,t2; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/main/innodb_mrr_cpk.result b/mysql-test/main/innodb_mrr_cpk.result index 28d7dd5..a2e43d7 100644 --- a/mysql-test/main/innodb_mrr_cpk.result +++ b/mysql-test/main/innodb_mrr_cpk.result @@ -226,7 +226,7 @@ set join_cache_level=3; explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 25 test.t1.id # Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 25 test.t1.id # 2 DERIVED t2 ALL NULL NULL NULL NULL # set join_cache_level= @tmp_mdev5037; drop table t0,t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cd8f3d0..5b08522 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -11618,7 +11618,15 @@ uint check_join_cache_usage(JOIN_TAB *tab, effort now. */ if (tab->table->pos_in_table_list->is_materialized_derived()) + { no_bka_cache= true; + /* + Don't use hash join algorithm if the temporary table for the rows + of the derived table will be created with an equi-join key. + */ + if (tab->table->s->keys) + no_hashed_cache= true; + } /* Don't use join buffering if we're dictated not to by no_jbuf_after

1 0

[Commits] 7247de3: MDEV-17382 Hash join algorithm should not be used to join materialized
by IgorBabaev 08 Oct '18

08 Oct '18

revision-id: 7247de382d38b1ac96cd95acb0757f0a1ebf9f5b (mariadb-10.3.10-9-g7247de3) parent(s): fbee31418cf08c62d91195b04a9ed6a42c31bcec author: Igor Babaev committer: Igor Babaev timestamp: 2018-10-08 04:24:37 -0700 message: MDEV-17382 Hash join algorithm should not be used to join materialized derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table. --- mysql-test/main/derived_opt.result | 35 +++++++++++++++++++++++++++++++- mysql-test/main/derived_opt.test | 38 +++++++++++++++++++++++++++++++++++ mysql-test/main/innodb_mrr_cpk.result | 2 +- sql/sql_select.cc | 8 ++++++++ 4 files changed, 81 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/derived_opt.result b/mysql-test/main/derived_opt.result index 6e4ea1b..0e8b49d 100644 --- a/mysql-test/main/derived_opt.result +++ b/mysql-test/main/derived_opt.result @@ -499,9 +499,42 @@ where D1.a= t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 5 test.t1.a 100 Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 2 DERIVED t2 ALL NULL NULL NULL NULL 100 Using filesort set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +# +# Bug mdev-17382: equi-join of derived table with join_cache_level=4 +# +CREATE TABLE t1 ( +id int NOT NULL, +amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); +set join_cache_level=4; +EXPLAIN +SELECT t2.id,t2.name,t.total_amt +FROM t2 +LEFT JOIN +(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t +ON t2.id=t.id +WHERE t2.id < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 3 Using index condition +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +set join_cache_level=default; +DROP TABLE t1,t2; set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/main/derived_opt.test b/mysql-test/main/derived_opt.test index 7f19553..aab95f6 100644 --- a/mysql-test/main/derived_opt.test +++ b/mysql-test/main/derived_opt.test @@ -363,5 +363,43 @@ set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +--echo # +--echo # Bug mdev-17382: equi-join of derived table with join_cache_level=4 +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); + +CREATE TABLE t2 ( + id int NOT NULL, + name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); + +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); + +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); + +set join_cache_level=4; + +EXPLAIN +SELECT t2.id,t2.name,t.total_amt + FROM t2 + LEFT JOIN + (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t + ON t2.id=t.id + WHERE t2.id < 3; + +set join_cache_level=default; + +DROP TABLE t1,t2; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/main/innodb_mrr_cpk.result b/mysql-test/main/innodb_mrr_cpk.result index 28d7dd5..a2e43d7 100644 --- a/mysql-test/main/innodb_mrr_cpk.result +++ b/mysql-test/main/innodb_mrr_cpk.result @@ -226,7 +226,7 @@ set join_cache_level=3; explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 25 test.t1.id # Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 25 test.t1.id # 2 DERIVED t2 ALL NULL NULL NULL NULL # set join_cache_level= @tmp_mdev5037; drop table t0,t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cd8f3d0..5b08522 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -11618,7 +11618,15 @@ uint check_join_cache_usage(JOIN_TAB *tab, effort now. */ if (tab->table->pos_in_table_list->is_materialized_derived()) + { no_bka_cache= true; + /* + Don't use hash join algorithm if the temporary table for the rows + of the derived table will be created with an equi-join key. + */ + if (tab->table->s->keys) + no_hashed_cache= true; + } /* Don't use join buffering if we're dictated not to by no_jbuf_after

1 0

[Commits] 1ebe841: MDEV-17382 Hash join algorithm should not be used to join materialized
by IgorBabaev 08 Oct '18

08 Oct '18

revision-id: 1ebe841fb85725c2a6aaecb3bca41ec098a5193d (mariadb-10.2.18-18-g1ebe841) parent(s): 1ff22b20624f233bf3ce785bd668d9a85cd8abe5 author: Igor Babaev committer: Igor Babaev timestamp: 2018-10-08 00:31:37 -0700 message: MDEV-17382 Hash join algorithm should not be used to join materialized derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table. --- mysql-test/r/derived_opt.result | 35 ++++++++++++++++++++++++++++++++++- mysql-test/r/innodb_mrr_cpk.result | 2 +- mysql-test/t/derived_opt.test | 38 ++++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 8 ++++++++ 4 files changed, 81 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index 6e4ea1b..0e8b49d 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -499,9 +499,42 @@ where D1.a= t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 5 test.t1.a 100 Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 2 DERIVED t2 ALL NULL NULL NULL NULL 100 Using filesort set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +# +# Bug mdev-17382: equi-join of derived table with join_cache_level=4 +# +CREATE TABLE t1 ( +id int NOT NULL, +amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); +set join_cache_level=4; +EXPLAIN +SELECT t2.id,t2.name,t.total_amt +FROM t2 +LEFT JOIN +(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t +ON t2.id=t.id +WHERE t2.id < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 3 Using index condition +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +set join_cache_level=default; +DROP TABLE t1,t2; set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result index 28d7dd5..a2e43d7 100644 --- a/mysql-test/r/innodb_mrr_cpk.result +++ b/mysql-test/r/innodb_mrr_cpk.result @@ -226,7 +226,7 @@ set join_cache_level=3; explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 25 test.t1.id # Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 25 test.t1.id # 2 DERIVED t2 ALL NULL NULL NULL NULL # set join_cache_level= @tmp_mdev5037; drop table t0,t1,t2; diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test index 7f19553..aab95f6 100644 --- a/mysql-test/t/derived_opt.test +++ b/mysql-test/t/derived_opt.test @@ -363,5 +363,43 @@ set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +--echo # +--echo # Bug mdev-17382: equi-join of derived table with join_cache_level=4 +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); + +CREATE TABLE t2 ( + id int NOT NULL, + name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); + +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); + +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); + +set join_cache_level=4; + +EXPLAIN +SELECT t2.id,t2.name,t.total_amt + FROM t2 + LEFT JOIN + (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t + ON t2.id=t.id + WHERE t2.id < 3; + +set join_cache_level=default; + +DROP TABLE t1,t2; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a270533..6f98bab 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -11138,7 +11138,15 @@ uint check_join_cache_usage(JOIN_TAB *tab, effort now. */ if (tab->table->pos_in_table_list->is_materialized_derived()) + { no_bka_cache= true; + /* + Don't use hash join algorithm if the temporary table for the rows + of the derived table will be created with an equi-join key. + */ + if (tab->table->s->keys) + no_hashed_cache= true; + } /* Don't use join buffering if we're dictated not to by no_jbuf_after

1 0

[Commits] cd9fe4b9586: Merge branch 'bb-10.1-sysprg-16656' into bb-10.1-galera
by jan 08 Oct '18

08 Oct '18

revision-id: cd9fe4b9586cd7fbc80394e48d93c5b5785ad80a (mariadb-10.1.35-82-gcd9fe4b9586) parent(s): cb02453ae5409c133fc4227899f25c44f4e8af99 49deadc737ceb03bf54002ad08e86d3002824c91 author: Jan Lindström committer: Jan Lindström timestamp: 2018-10-08 09:13:37 +0300 message: Merge branch 'bb-10.1-sysprg-16656' into bb-10.1-galera include/my_pthread.h | 2 +- .../suite/galera/t/galera_drop_database.test | 4 +- storage/innobase/include/ut0counter.h | 46 +++++++++++++--------- storage/xtradb/include/ut0counter.h | 46 +++++++++++++--------- 4 files changed, 58 insertions(+), 40 deletions(-)

1 0

[Commits] 1eca495: MDEV-17382 Hash join algorithm should not be used to join materialized
by IgorBabaev 07 Oct '18

07 Oct '18

revision-id: 1eca49577e979220f3ab663a7e46e0eb70d728c4 (mariadb-10.1.35-80-g1eca495) parent(s): 079d0a8724aa3faa3117554c44b81965df457ef3 author: Igor Babaev committer: Igor Babaev timestamp: 2018-10-07 14:42:22 -0700 message: MDEV-17382 Hash join algorithm should not be used to join materialized derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table. --- mysql-test/r/derived_opt.result | 35 ++++++++++++++++++++++++++++++++++- mysql-test/r/innodb_mrr_cpk.result | 2 +- mysql-test/t/derived_opt.test | 38 ++++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 8 ++++++++ 4 files changed, 81 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index 04a76c2..63d2c43 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -499,9 +499,42 @@ where D1.a= t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 5 test.t1.a 100 Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 2 DERIVED t2 ALL NULL NULL NULL NULL 100 Using filesort set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +# +# Bug mdev-17382: equi-join of derived table with join_cache_level=4 +# +CREATE TABLE t1 ( +id int NOT NULL, +amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); +set join_cache_level=4; +EXPLAIN +SELECT t2.id,t2.name,t.total_amt +FROM t2 +LEFT JOIN +(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t +ON t2.id=t.id +WHERE t2.id < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 3 Using index condition +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +set join_cache_level=default; +DROP TABLE t1,t2; set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result index 28d7dd5..a2e43d7 100644 --- a/mysql-test/r/innodb_mrr_cpk.result +++ b/mysql-test/r/innodb_mrr_cpk.result @@ -226,7 +226,7 @@ set join_cache_level=3; explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 25 test.t1.id # Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 25 test.t1.id # 2 DERIVED t2 ALL NULL NULL NULL NULL # set join_cache_level= @tmp_mdev5037; drop table t0,t1,t2; diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test index 7f19553..aab95f6 100644 --- a/mysql-test/t/derived_opt.test +++ b/mysql-test/t/derived_opt.test @@ -363,5 +363,43 @@ set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +--echo # +--echo # Bug mdev-17382: equi-join of derived table with join_cache_level=4 +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); + +CREATE TABLE t2 ( + id int NOT NULL, + name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); + +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); + +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); + +set join_cache_level=4; + +EXPLAIN +SELECT t2.id,t2.name,t.total_amt + FROM t2 + LEFT JOIN + (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t + ON t2.id=t.id + WHERE t2.id < 3; + +set join_cache_level=default; + +DROP TABLE t1,t2; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fa0be81..62f40ee 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -11005,7 +11005,15 @@ uint check_join_cache_usage(JOIN_TAB *tab, effort now. */ if (tab->table->pos_in_table_list->is_materialized_derived()) + { no_bka_cache= true; + /* + Don't use hash join algorithm if the temporary table for the rows + of the derived table will be created with an equi-join key. + */ + if (tab->table->s->keys) + no_hashed_cache= true; + } /* Don't use join buffering if we're dictated not to by no_jbuf_after

1 0

[Commits] bd21904: MDEV-17382 Hash join algorithm should not be used to join materialized
by IgorBabaev 07 Oct '18

07 Oct '18

revision-id: bd21904357d95631fbbb15defe4b023dce6a24a2 (mariadb-10.0.36-36-gbd21904) parent(s): a660a5ed42ace61284774b013d45dd4101f831bf author: Igor Babaev committer: Igor Babaev timestamp: 2018-10-07 13:12:27 -0700 message: MDEV-17382 Hash join algorithm should not be used to join materialized derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table. --- mysql-test/r/derived_opt.result | 35 ++++++++++++++++++++++++++++++++++- mysql-test/r/innodb_mrr_cpk.result | 2 +- mysql-test/t/derived_opt.test | 38 ++++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 8 ++++++++ 4 files changed, 81 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index 04a76c2..63d2c43 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -499,9 +499,42 @@ where D1.a= t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 5 test.t1.a 100 Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 2 DERIVED t2 ALL NULL NULL NULL NULL 100 Using filesort set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +# +# Bug mdev-17382: equi-join of derived table with join_cache_level=4 +# +CREATE TABLE t1 ( +id int NOT NULL, +amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); +set join_cache_level=4; +EXPLAIN +SELECT t2.id,t2.name,t.total_amt +FROM t2 +LEFT JOIN +(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t +ON t2.id=t.id +WHERE t2.id < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 3 Using index condition +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +set join_cache_level=default; +DROP TABLE t1,t2; set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result index 99ed73a..355eae7 100644 --- a/mysql-test/r/innodb_mrr_cpk.result +++ b/mysql-test/r/innodb_mrr_cpk.result @@ -226,7 +226,7 @@ set join_cache_level=3; explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # -1 PRIMARY <derived2> hash_ALL key0 #hash#key0 25 test.t1.id # Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ref key0 key0 25 test.t1.id # 2 DERIVED t2 ALL NULL NULL NULL NULL # set join_cache_level= @tmp_mdev5037; drop table t0,t1,t2; diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test index 7f19553..aab95f6 100644 --- a/mysql-test/t/derived_opt.test +++ b/mysql-test/t/derived_opt.test @@ -363,5 +363,43 @@ set join_cache_level=@tmp_jcl; set optimizer_switch=@tmp_os; drop table t1, t2; +--echo # +--echo # Bug mdev-17382: equi-join of derived table with join_cache_level=4 +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + amount decimal DEFAULT NULL, +PRIMARY KEY (id) +); + +CREATE TABLE t2 ( + id int NOT NULL, + name varchar(50) DEFAULT NULL, +PRIMARY KEY (id) +); + +INSERT INTO t1 VALUES +(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), +(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000); + +INSERT INTO t2 VALUES +(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL), +(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL); + +set join_cache_level=4; + +EXPLAIN +SELECT t2.id,t2.name,t.total_amt + FROM t2 + LEFT JOIN + (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t + ON t2.id=t.id + WHERE t2.id < 3; + +set join_cache_level=default; + +DROP TABLE t1,t2; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b49a95e..ca9a6a4 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10785,7 +10785,15 @@ uint check_join_cache_usage(JOIN_TAB *tab, effort now. */ if (tab->table->pos_in_table_list->is_materialized_derived()) + { no_bka_cache= true; + /* + Don't use hash join algorithm if the temporary table for the rows + of the derived table will be created with an equi-join key. + */ + if (tab->table->s->keys) + no_hashed_cache= true; + } /* Don't use join buffering if we're dictated not to by no_jbuf_after

1 0

[Commits] 645d8a6: MDEV-17360 Server crashes in optimize_keyuse
by IgorBabaev 07 Oct '18

07 Oct '18

revision-id: 645d8a639d9dad265dd2c6da947beaac11969159 (mariadb-10.3.6-146-g645d8a6) parent(s): 55dd0776566000c5ea12e177df0c784b500ab7c1 author: Igor Babaev committer: Igor Babaev timestamp: 2018-10-07 12:16:59 -0700 message: MDEV-17360 Server crashes in optimize_keyuse This was a bug in the code of MDEV-12387 "Push conditions into materialized subqueries". The bug manifested itself in rather rare situations. An affected query must contain IN subquery predicate whose left operand was an outer field of a mergeable derived table or view and right operand was a materialized subquery. The erroneous code in fact stripped off the Item_direct_ref wrapper from the left operand of the IN subquery predicate when building equalities produced by the conversion of the predicate into a semi-join. As a result the left operand was not considered as an outer reference anymore and used_tables() was calculated incorrectly. This caused a crash in the function optimize_keyuse(). --- mysql-test/main/in_subq_cond_pushdown.result | 13 +++++++++++++ mysql-test/main/in_subq_cond_pushdown.test | 17 +++++++++++++++++ sql/opt_subselect.cc | 6 +++--- 3 files changed, 33 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result index c9319a5..077edf7 100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@ -3855,3 +3855,16 @@ ORDER BY t4.d a b c x x NULL DROP TABLE t1,t2,t3,t4; +# +# MDEV-17360: IN subquery predicate with outer reference in the left part +# that refers to a field of a mergeable derived table +# +CREATE TABLE t1 (id1 int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1814),(0),(NULL),(1); +CREATE TABLE t2 (id2 int) ENGINE=MYISAM; +SELECT 1 AS r FROM t2,t1,(SELECT * FROM t1) dt1 +WHERE NOT EXISTS (SELECT id2 FROM t2 +WHERE dt1.id1 IN (SELECT t2.id2 FROM t2 +HAVING t2.id2 >= 1)); +r +DROP TABLE t1,t2; diff --git a/mysql-test/main/in_subq_cond_pushdown.test b/mysql-test/main/in_subq_cond_pushdown.test index 3c673fb..4c0dd3d 100644 --- a/mysql-test/main/in_subq_cond_pushdown.test +++ b/mysql-test/main/in_subq_cond_pushdown.test @@ -821,3 +821,20 @@ WHERE (t1.a) IN ); DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # MDEV-17360: IN subquery predicate with outer reference in the left part +--echo # that refers to a field of a mergeable derived table +--echo # + +CREATE TABLE t1 (id1 int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1814),(0),(NULL),(1); + +CREATE TABLE t2 (id2 int) ENGINE=MYISAM; + +SELECT 1 AS r FROM t2,t1,(SELECT * FROM t1) dt1 + WHERE NOT EXISTS (SELECT id2 FROM t2 + WHERE dt1.id1 IN (SELECT t2.id2 FROM t2 + HAVING t2.id2 >= 1)); + +DROP TABLE t1,t2; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index d1877c6..4eeaefa 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5512,7 +5512,7 @@ int select_value_catcher::send_data(List<Item> &items) /** @brief - Conjugate conditions after optimize_cond() call + Add new conditions after optimize_cond() call @param thd the thread handle @param cond the condition where to attach new conditions @@ -5561,8 +5561,8 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, Item::Context(Item::ANY_SUBST, ((Item_func_equal *)item)->compare_type_handler(), ((Item_func_equal *)item)->compare_collation()), - ((Item_func *)item)->arguments()[0]->real_item(), - ((Item_func *)item)->arguments()[1]->real_item(), + ((Item_func *)item)->arguments()[0], + ((Item_func *)item)->arguments()[1], &new_cond_equal)) li.remove(); }

1 0