summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/sp.result38
-rw-r--r--mysql-test/r/subselect4.result21
-rw-r--r--mysql-test/r/subselect_innodb.result18
-rw-r--r--mysql-test/r/sum_distinct-big.result15
-rw-r--r--mysql-test/r/type_year.result1
-rw-r--r--mysql-test/suite/vcol/r/vcol_misc.result11
-rw-r--r--mysql-test/suite/vcol/t/vcol_misc.test19
-rw-r--r--mysql-test/t/sp.test20
-rw-r--r--mysql-test/t/subselect4.test24
-rw-r--r--mysql-test/t/subselect_innodb.test14
-rw-r--r--mysql-test/t/sum_distinct-big.test17
11 files changed, 195 insertions, 3 deletions
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index b5e6e84a49a..defd3955f6c 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -6530,16 +6530,16 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c1 c1 5 const 1 Using index
EXPLAIN SELECT * FROM t1 WHERE c1=f1();
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref c1 c1 5 const 0 Using where; Using index
+1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
EXPLAIN SELECT * FROM v1 WHERE c1=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref c1 c1 5 const 1 Using index
EXPLAIN SELECT * FROM v1 WHERE c1=f1();
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref c1 c1 5 const 0 Using where; Using index
+1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref c1 c1 5 const 0 Using where; Using index
+1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index
@@ -7852,6 +7852,38 @@ c1 c2 count(c3)
2012-03-01 01:00:00 3 1
2012-03-01 02:00:00 3 1
DROP PROCEDURE p1;
+
+MDEV-3900 Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements
+
+CREATE FUNCTION tdn() RETURNS int(7) DETERMINISTIC RETURN to_days(now());
+CREATE TABLE t1 (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, daynum INT, a CHAR(1), INDEX(daynum), INDEX(a)) ENGINE=MyISAM;
+INSERT INTO t1 (daynum) VALUES (1),(2),(3),(4),(5),(TO_DAYS(NOW())),(7),(8);
+INSERT INTO t1 (daynum) SELECT a1.daynum FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5;
+FLUSH TABLES;
+FLUSH STATUS;
+SHOW STATUS LIKE '%Handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 0
+Handler_read_last 0
+Handler_read_next 0
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 0
+UPDATE t1 SET a = '+' WHERE daynum=tdn();
+SHOW STATUS LIKE '%Handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 2
+Handler_read_last 0
+Handler_read_next 4097
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 0
+drop function tdn;
+drop table t1;
#
# lp:1002157 : testing stored function
# bug#62125 result for null incorrectly yields 1292 warning.
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 7f507b7e2d1..2814e5a6dcd 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2274,6 +2274,27 @@ SELECT a3 FROM t3 WHERE b2 = b1 AND b2 <= b1 ORDER BY b3
a1 b1
drop table t1, t2, t3;
#
+# MDEV-4056:Server crashes in Item_func_trig_cond::val_int
+# with FROM and NOT IN subqueries, LEFT JOIN, derived_merge+in_to_exists
+#
+set @optimizer_switch_MDEV4056 = @@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on,in_to_exists=on';
+CREATE TABLE t1 (a VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('x'),('d');
+CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2,'v'),(2,150,'v');
+SELECT * FROM t1 LEFT JOIN (
+SELECT * FROM t2 WHERE ( pk, pk ) NOT IN (
+SELECT MIN(b), SUM(pk) FROM t1
+)
+) AS alias1 ON (a = c)
+WHERE b IS NULL OR a < 'u';
+a pk b c
+x NULL NULL NULL
+d NULL NULL NULL
+drop table t1,t2;
+set @@optimizer_switch = @optimizer_switch_MDEV4056;
+#
# MDEV-3899 Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT
#
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index a4670872fad..a6967527a2d 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -391,4 +391,22 @@ select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 gr
1
1
drop table t1;
+#
+# MDEV-3988 crash in create_tmp_table
+#
+drop table if exists `t1`,`t2`;
+Warnings:
+Note 1051 Unknown table 't1'
+Note 1051 Unknown table 't2'
+create table `t1`(`a` char(1) character set utf8)engine=innodb;
+create table `t2`(`b` char(1) character set utf8)engine=memory;
+select distinct (select 1 from `t2` where `a`) `d2` from `t1`;
+d2
+select distinct (select 1 from `t2` where `a`) `d2`, a from `t1`;
+d2 a
+select distinct a, (select 1 from `t2` where `a`) `d2` from `t1`;
+a d2
+select distinct (1 + (select 1 from `t2` where `a`)) `d2` from `t1`;
+d2
+drop table t1,t2;
set optimizer_switch=@subselect_innodb_tmp;
diff --git a/mysql-test/r/sum_distinct-big.result b/mysql-test/r/sum_distinct-big.result
index 9b55d59ab91..d4933b31f80 100644
--- a/mysql-test/r/sum_distinct-big.result
+++ b/mysql-test/r/sum_distinct-big.result
@@ -103,5 +103,20 @@ sm
10323810
10325070
10326330
+#
+# Bug mdev-4063: SUM(DISTINCT...) with small'max_heap_table_size
+# (bug #56927)
+#
+SET max_heap_table_size=default;
+INSERT INTO t1 SELECT id+16384 FROM t1;
+DELETE FROM t2;
+INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
+SELECT SUM(DISTINCT id) sm FROM t2;
+sm
+536887296
+SET max_heap_table_size=16384;
+SELECT SUM(DISTINCT id) sm FROM t2;
+sm
+536887296
DROP TABLE t1;
DROP TABLE t2;
diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result
index 4b9bf6f433f..38bddd42cfa 100644
--- a/mysql-test/r/type_year.result
+++ b/mysql-test/r/type_year.result
@@ -389,6 +389,7 @@ select a from t1 where a=(select 2000 from dual where 1);
a
select a from t1 where a=y2k();
a
+00
select a from t1 where a=b;
a
drop table t1;
diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result
index 17b38b7d7af..14467b2d630 100644
--- a/mysql-test/suite/vcol/r/vcol_misc.result
+++ b/mysql-test/suite/vcol/r/vcol_misc.result
@@ -182,6 +182,17 @@ a b c
2 3 y
0 1 y,n
drop table t1,t2;
+CREATE TABLE t1 (
+ts TIMESTAMP,
+tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL
+) ENGINE=MyISAM;
+INSERT INTO t1 (tsv) VALUES (DEFAULT);
+INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT);
+FLUSH TABLES;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+2
+DROP TABLE t1;
create table t1 (a int, b int);
insert into t1 values (3, 30), (4, 20), (1, 20);
create table t2 (c int, d int, v int as (d+1), index idx(c));
diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test
index 01404046cde..3c51ee4b685 100644
--- a/mysql-test/suite/vcol/t/vcol_misc.test
+++ b/mysql-test/suite/vcol/t/vcol_misc.test
@@ -179,6 +179,25 @@ select * from t2;
drop table t1,t2;
#
+# Bug mdev-3938: INSERT DELAYED for a table with virtual columns
+#
+
+CREATE TABLE t1 (
+ ts TIMESTAMP,
+ tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL
+) ENGINE=MyISAM;
+
+INSERT INTO t1 (tsv) VALUES (DEFAULT);
+
+INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT);
+
+FLUSH TABLES;
+
+SELECT COUNT(*) FROM t1;
+
+DROP TABLE t1;
+
+#
# SELECT that uses a virtual column and executed with BKA
#
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 2528f0d7b2b..0a368826ee7 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -9151,6 +9151,26 @@ CALL p1(1);
DROP PROCEDURE p1;
+--echo
+--echo MDEV-3900 Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements
+--echo
+
+CREATE FUNCTION tdn() RETURNS int(7) DETERMINISTIC RETURN to_days(now());
+
+CREATE TABLE t1 (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, daynum INT, a CHAR(1), INDEX(daynum), INDEX(a)) ENGINE=MyISAM;
+INSERT INTO t1 (daynum) VALUES (1),(2),(3),(4),(5),(TO_DAYS(NOW())),(7),(8);
+INSERT INTO t1 (daynum) SELECT a1.daynum FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5;
+
+FLUSH TABLES;
+FLUSH STATUS;
+
+SHOW STATUS LIKE '%Handler_read%';
+UPDATE t1 SET a = '+' WHERE daynum=tdn();
+SHOW STATUS LIKE '%Handler_read%';
+
+drop function tdn;
+drop table t1;
+
--echo #
--echo # lp:1002157 : testing stored function
--echo # bug#62125 result for null incorrectly yields 1292 warning.
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index d939c60ddd1..37f660d6682 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1805,6 +1805,30 @@ SELECT * FROM t1 WHERE a1 IN (
drop table t1, t2, t3;
--echo #
+--echo # MDEV-4056:Server crashes in Item_func_trig_cond::val_int
+--echo # with FROM and NOT IN subqueries, LEFT JOIN, derived_merge+in_to_exists
+--echo #
+
+set @optimizer_switch_MDEV4056 = @@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on,in_to_exists=on';
+
+CREATE TABLE t1 (a VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('x'),('d');
+
+CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2,'v'),(2,150,'v');
+
+SELECT * FROM t1 LEFT JOIN (
+ SELECT * FROM t2 WHERE ( pk, pk ) NOT IN (
+ SELECT MIN(b), SUM(pk) FROM t1
+ )
+) AS alias1 ON (a = c)
+WHERE b IS NULL OR a < 'u';
+
+drop table t1,t2;
+set @@optimizer_switch = @optimizer_switch_MDEV4056;
+
+--echo #
--echo # MDEV-3899 Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT
--echo #
diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test
index 3af8f31062c..83c36b16163 100644
--- a/mysql-test/t/subselect_innodb.test
+++ b/mysql-test/t/subselect_innodb.test
@@ -377,4 +377,18 @@ select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 gr
drop table t1;
+--echo #
+--echo # MDEV-3988 crash in create_tmp_table
+--echo #
+
+drop table if exists `t1`,`t2`;
+create table `t1`(`a` char(1) character set utf8)engine=innodb;
+create table `t2`(`b` char(1) character set utf8)engine=memory;
+select distinct (select 1 from `t2` where `a`) `d2` from `t1`;
+select distinct (select 1 from `t2` where `a`) `d2`, a from `t1`;
+select distinct a, (select 1 from `t2` where `a`) `d2` from `t1`;
+select distinct (1 + (select 1 from `t2` where `a`)) `d2` from `t1`;
+
+drop table t1,t2;
+
set optimizer_switch=@subselect_innodb_tmp;
diff --git a/mysql-test/t/sum_distinct-big.test b/mysql-test/t/sum_distinct-big.test
index 0859f4b3d89..d3710056c9a 100644
--- a/mysql-test/t/sum_distinct-big.test
+++ b/mysql-test/t/sum_distinct-big.test
@@ -63,5 +63,22 @@ SELECT SUM(DISTINCT id) sm FROM t1;
SELECT SUM(DISTINCT id) sm FROM t2;
SELECT SUM(DISTINCT id) sm FROM t1 GROUP BY id % 13;
+--echo #
+--echo # Bug mdev-4063: SUM(DISTINCT...) with small'max_heap_table_size
+--echo # (bug #56927)
+--echo #
+
+SET max_heap_table_size=default;
+
+INSERT INTO t1 SELECT id+16384 FROM t1;
+DELETE FROM t2;
+INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
+
+SELECT SUM(DISTINCT id) sm FROM t2;
+
+SET max_heap_table_size=16384;
+
+SELECT SUM(DISTINCT id) sm FROM t2;
+
DROP TABLE t1;
DROP TABLE t2;
View Lorry Controller Status