From 9419908f3889e20d8edd299ca714fd779613cf5e Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Sat, 4 Aug 2018 05:57:06 +0530 Subject: MDEV-15433: Optimizer does not use group by optimization with distinct After the commit b76b69cd5fe634d8ddb9406aa2c82ef2a375b4d8 loose index scan for queries with DISTINCT stopped working. That is why that commit has to be reverted. Additionally this patch fixes the problem of MDEV-10880. --- mysql-test/t/group_min_max.test | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index b0bc42d7f8c..adad9073235 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1608,6 +1608,33 @@ explain select min(a) from t1 where a between "a" and "Cafeeeeeeeeeeeeeeeeeeeeee explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2"; drop table t1; +--echo # +--echo # MDEV-15433: Optimizer does not use group by optimization with distinct +--echo # + +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); +--disable_query_log +INSERT INTO t1(a) VALUES (1), (2), (3), (4); +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +--enable_query_log +OPTIMIZE TABLE t1; +EXPLAIN SELECT DISTINCT a FROM t1; +SELECT DISTINCT a FROM t1; +drop table t1; + --echo # --echo # End of 10.1 tests --echo # -- cgit v1.2.1 From 3ff0801c7397e3ae5fc538ffca3d58891cd4f27b Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Sat, 11 Aug 2018 12:11:59 +0200 Subject: MDEV-16810 AddressSanitizer: stack-buffer-overflow in int10_to_str truncate incorrect values in convert_period_to_month() so that PERIOD_DIFF never returns a value outside of 2^23 range. And, for safety, increase buffer sizes for int10_to_str to be sufficienly big for any int10_to_str result. --- mysql-test/t/func_time.test | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 8323bd30d2c..361eff170fb 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1629,6 +1629,10 @@ DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); SELECT TO_DAYS(SEC_TO_TIME(MAKEDATE(0,RAND(~0)))); SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); +# +# MDEV-16810 AddressSanitizer: stack-buffer-overflow in int10_to_str +# +SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); --echo # --echo # End of 5.5 tests -- cgit v1.2.1 From 68eb9b1a78423b9c0a353bc1bdfa7b518977dbac Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Tue, 14 Aug 2018 22:42:46 +0200 Subject: MDEV-16220 Do not pass UTF8 to mysql in command line parameters, on Windows Moved parts of mysql.test to mysql_not_windows.test --- mysql-test/t/mysql.test | 5 +---- mysql-test/t/mysql_not_windows.test | 7 +++++++ 2 files changed, 8 insertions(+), 4 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index fc2ae849aa6..e3219c21517 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -638,10 +638,7 @@ EOF --echo # End of 10.1 tests --echo # ---error 1 ---exec $MYSQL --default-character-set=utf8 -e "select 1" "test😁 " 2>&1 ---error 1 ---exec $MYSQL --default-character-set=binary -e "select 1" "test😁 " 2>&1 + --write_file $MYSQLTEST_VARDIR/tmp/mdev-6572.sql SET NAMES utf8; USE test😁 ; diff --git a/mysql-test/t/mysql_not_windows.test b/mysql-test/t/mysql_not_windows.test index 591de74cbbf..816160c4f3e 100644 --- a/mysql-test/t/mysql_not_windows.test +++ b/mysql-test/t/mysql_not_windows.test @@ -22,3 +22,10 @@ exec $MYSQL test -e "select let $query = select 3 as X; exec $MYSQL test -e "$query"; + +# Not ran on Windows, since non-ASCII does not work on command line. +# (MDEV-16220) +--error 1 +--exec $MYSQL --default-character-set=utf8 -e "select 1" "test😁 " 2>&1 +--error 1 +--exec $MYSQL --default-character-set=binary -e "select 1" "test😁 " 2>&1 -- cgit v1.2.1 From 1b797e9e6308913c2472f3e04ad253e95a35d59f Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Mon, 6 Aug 2018 15:50:22 +0200 Subject: MDEV-15475: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed on EXPLAIN EXTENDED with constant table and view Print constant ISNULL value independent. Fix of printing of view FRM and CREATE VIEW output --- mysql-test/t/func_isnull.test | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test index 4c59fa3cbe8..7d1a7e83a1a 100644 --- a/mysql-test/t/func_isnull.test +++ b/mysql-test/t/func_isnull.test @@ -83,6 +83,22 @@ SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL; DROP VIEW v1; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-15475: Assertion `!table || (!table->read_set || +--echo # bitmap_is_set(table->read_set, field_index))' +--echo # failed on EXPLAIN EXTENDED with constant table and view +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1); +EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; +# Cleanup +DROP VIEW v1; +DROP TABLE t1; + --echo # --echo # End of 5.5 tests --echo # -- cgit v1.2.1 From b62ac161856570e9a0e92d17de1e3dd31d54410f Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Wed, 15 Aug 2018 15:21:37 +0300 Subject: MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset Item_subselect::is_expensive() used to return FALSE (Inexpensive) whenever it saw that one of SELECTs in the Subquery's UNION is degenerate. It ignored the fact that other parts of the UNION might not be inexpensive, including the case where pther parts of the UNION have no query plan yet. For a subquery in form col >= ANY (SELECT 'foo' UNION SELECT 'bar') this would cause the query to be considered inexpensive when there is no query plan for the second part of the UNION, which in turn would cause the SELECT 'foo' to compute and free itself while still inside JOIN::optimize for that SELECT (See MDEV comment for full description). --- mysql-test/t/subselect_extra_no_semijoin.test | 31 ++++++++++++++++++++++++++- 1 file changed, 30 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_extra_no_semijoin.test b/mysql-test/t/subselect_extra_no_semijoin.test index 8aba3dde72b..d8809c7f981 100644 --- a/mysql-test/t/subselect_extra_no_semijoin.test +++ b/mysql-test/t/subselect_extra_no_semijoin.test @@ -6,4 +6,33 @@ set @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loo set optimizer_switch= @subselect_extra_no_sj_tmp; -set @optimizer_switch_for_subselect_extra_test=null; \ No newline at end of file +set @optimizer_switch_for_subselect_extra_test=null; + +--echo # +--echo # MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset +--echo # + +## Using a separate client connection is easier than restoring state +connect(con1,localhost,root,,); + +SET NAMES tis620; +set @tmp= @@global.slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000001; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); + +create table t2 (b int); +insert into t2 values (1),(2),(3); + +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); + +drop table t1,t2; +SET GLOBAL slow_query_log=@tmp; +disconnect con1; +connection default; -- cgit v1.2.1